Creating types with PostgreSQL - part 1 - basics

Creating types and tables using PostgreSQL

SQL sentences video (except for create database)

-- We're going to create a database to store the electronic documentation
-- For each document we need to know
-- file name (name + extension)
-- who has created the document with some contact data
-- version (example: 3.1, 5.2, ...)
-- summary info
-- date of creation

DROP TABLE IF EXISTS tbl_docs;
DROP TYPE IF EXISTS tp_file;
DROP TYPE IF EXISTS tp_version;
DROP TYPE IF EXISTS tp_fileName;
DROP TABLE IF EXISTS tbl_author;
DROP TYPE IF EXISTS tp_author;


-- First we create the type for author
CREATE TYPE tp_author as (
ID char(10),
name varchar(100),
telf varchar(12),
email varchar(255)
);

-- Creating the table to store the authors collaborators

CREATE TABLE tbl_author OF tp_author (
PRIMARY KEY (ID)
);

-- Inserting some authors


INSERT INTO tbl_author VALUES (
'0000000001', -- ID,
'Joan Pi', -- NAME
'659...', -- TELEPHONE
'[email protected]' -- EMAIL
);

SELECT * FROM tbl_author;


-- Creating filename and file types
-- file name (name + extension)

CREATE TYPE tp_fileName AS (
name varchar(200),
extension varchar(10)
);

-- We need to create version type
-- version (example: 3.1, 5.2, ...)
-- major and minor

CREATE TYPE tp_version AS (
major int,
minor int
);


-- Info related to type file
-- file name (name + extension)
-- who has created the document with some contact data
-- version (example: 3.1, 5.2, ...)
-- summary info
-- date of creation


CREATE TYPE tp_file AS (
name tp_filename,
description text, -- summary info
idAuthor char(10), -- field related to authors table. We associated on table creation
createdDate timestamp with time zone, -- date of creation
version tp_version -- Version of the document
);

-- Creating the table to store the document's info

CREATE TABLE tbl_docs of tp_file (
PRIMARY KEY (name,version),
CONSTRAINT FK_AUTHOR -- Associating idAuthor with ID field of tbl_author
FOREIGN KEY (idAuthor) REFERENCES tbl_author(ID)
);


-- INSERTING SOME DATA ON tbl_docs

INSERT INTO tbl_docs VALUES (
ROW('Requirements','odt')::tp_filename, -- name,extension. specify ::tp_filename is optional
'Requirements ....', -- description
'0000000001', -- author identification. Must exist in tbl_author
'2018-07-03 12:25:00', -- creation date
ROW(3,1) -- version . In this case cast (::tp_version) not specified
);


SELECT * FROM tbl_docs;

#FpInfor #Dam #DamMp02 #DamMp02Uf4