Creating types with postgres - part 2 - Inheritance

Published: 28 July 2018
on channel: Miquel Boada Artigas

993

Using inheritance with PostgreSQL to extend a table with new columns .

-- 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_video;
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;

-- *******************************************************************************
-- ******************************** INHERITANCE ********************************
-- *******************************************************************************

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

SELECT * FROM tbl_docs where (name).name='Setup' and (name).extension='mp3';

-- Inserts on tbl_video generates generates 2 inserts: on in tbl_docs and another in tbl_video

INSERT INTO tbl_video VALUES (
ROW('Setup','mp3')::tp_filename, -- name,extension. specify ::tp_filename is optional
'How to ...', -- description
'0000000001', -- author identification. Must exist in tbl_author
'2018-07-20 10:00:00', -- creation date
ROW(1,2), -- version . In this case cast (::tp_version) not specified
'0:1:15' -- 0 hours 1 minute and 15 seconds
);

SELECT * FROM tbl_video where (name).name='Setup' and (name).extension='mp3';

#FpInfor #Dam #DamMp02 #DamMp02Uf4

Watch video Creating types with postgres - part 2 - Inheritance online without registration, duration hours minute second in high quality. This video was added by user Miquel Boada Artigas 28 July 2018, don't forget to share it with your friends and acquaintances, it has been viewed on our site 993 once and liked it 2 people.