Creating types with postgres - part 2 - Inheritance

Опубликовано: 28 Июль 2018
на канале: 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

Смотрите видео Creating types with postgres - part 2 - Inheritance онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Miquel Boada Artigas 28 Июль 2018, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 993 раз и оно понравилось 2 людям.

4,897