Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Stored Procedure and Function concepts in PostgreSQL are not the same as in other relational databases.
Functions are used like stored procedures.
In some places you can hear it as a Stored function.
Below you can find a function script that does not return value.
This way you can use functions like stored procedures.
1 2 3 4 5 6 7 8 9 | --Create Table Create table PersonListTable(name VARCHAR(70)); --Create Stored Procedure CREATE OR REPLACE FUNCTION addrecord(name VARCHAR(70)) RETURNS void AS $$ BEGIN INSERT INTO PersonListTable VALUES (name); END; $$ LANGUAGE plpgsql; |
If you run the following script after creating the function, the corresponding record will be added to the table.
1 | SELECT addrecord('Rick'); |
You can then use the following script to check the table.
1 | select * from PersonListTable; |
Below you can see a function script that returns a result set.
1 2 3 4 5 6 7 8 | CREATE OR REPLACE FUNCTION listpersons() RETURNS refcursor AS $$ DECLARE ref refcursor; BEGIN OPEN ref FOR SELECT name FROM PersonListTable; RETURN ref; END; $$ LANGUAGE plpgsql; |
You can execute it as follows.
1 2 | BEGIN; SELECT listpersons(); |
The result will be as follows.
<unnamed portal 1>
Then you can get the result by script as below.
1 2 | FETCH ALL IN "<unnamed portal 4>"; COMMIT; |
