How To Create Stored Procedure,Function On PostgreSQL

dbtut

6 years ago

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.

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

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

SELECT addrecord('Rick');

1	SELECT addrecord('Rick');

You can then use the following script to check the table.

select * from PersonListTable;

1	select * from PersonListTable;

Below you can see a function script that returns a result set.

CREATE OR REPLACE FUNCTION listpersons() RETURNS refcursor AS $$
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT name FROM PersonListTable;
RETURN ref;
END;
$$ LANGUAGE plpgsql;

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.

BEGIN;
SELECT listpersons();

1 2	BEGIN; SELECT listpersons();

The result will be as follows.

Then you can get the result by script as below.

FETCH ALL IN "<unnamed portal 4>";
COMMIT;

1 2	FETCH ALL IN "<unnamed portal 4>"; COMMIT;