Creating functions with PostgreSQL

How to create and run functions in PostgreSQL

Code used in the video

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

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

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


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

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

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


-- With PostgreSQL we can create functions to add functionality to our database. You can execute from an external
-- program for example using Java language or from a PostgreSQL client.

-- In this video we're going to use a previous database. We've included the code above.
-- The purpose of our function is to get the filename as string. The filename is composed in two parts:
-- the name and the extension.
-- f(x)=name + '.' + extension

CREATE OR REPLACE FUNCTION fn_filename(name tp_filename) RETURNS text AS $$
BEGIN
RETURN (name).name || '.' || (name).extension; -- concat operator in PostgreSQL is ||
END
$$ LANGUAGE plpgsql;


-- Getting name and description from tbl_docs

SELECT name, description from tbl_docs; -- name column returns data in a format object

-- Using the function we get the filename as name.extension (Requirements.dot)

SELECT fn_filename(name) AS filename, description FROM tbl_docs;



#FpInfor #Dam #DamMp02 #DamMp02Uf4