Q01 What is the difference between SQL stored Procedures & Functions?
A01 Stored procedure does not have a RETURNS clause, but you can use the OUT or INOUT parameter. Stored procedures are transactional whereas functions are not. Apart from these differences, the stored procedures are similar to functions.
You can use functions to update and retrieve data, or to just perform a procedure by returning a type void.
Example 1: plpgsql procedure
Schema SQL in DB-FIDDLE. Select PostgreSQL v17 as the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE IF NOT EXISTS tbl_result(value integer); CREATE OR REPLACE PROCEDURE sum(IN a Integer, IN b Integer, OUT c integer) LANGUAGE plpgsql AS $$ DECLARE result Integer; BEGIN result := a + b; c := result; INSERT INTO tbl_result(value) VALUES (result); COMMIT; -- transaction committed END; $$; |
Query SQL
|
1 2 3 4 |
CALL sum(2,3, null); SELECT * FROM tbl_result; |
Note that null needs to be passed for the OUT parameter as well as it is in the procedure definition.
Outputs:
|
1 2 3 4 5 6 7 8 9 |
c ------ 5 value ------- 5 |
Example 2: plpgsql function
A function can use a RETURNS clause or use an OUT parameter as above. It can also return a void as shown below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE IF NOT EXISTS tbl_result(value integer); CREATE OR REPLACE FUNCTION sum(IN a Integer, IN b Integer) RETURNS void LANGUAGE plpgsql AS $$ DECLARE result Integer; BEGIN result := a + b; INSERT INTO tbl_result(value) VALUES (result); END; $$; |
Query SQL
|
1 2 3 4 |
SELECT sum(2,3); SELECT * FROM tbl_result; |
Q02 What are $$ in PostgreSQL?
A02 It is used for dollar-quoting, which allows defining string literals or code blocks without escaping special characters like single quotes. It is commonly used in PL/pgSQL functions to simplify the handling of complex text or SQL code.
Instead of $$, you can also use $some string$ as in $body$ or $func$ as shown below:
Example 3: plpgsql function with $body$
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE OR REPLACE FUNCTION sum(IN a Integer, IN b Integer) RETURNS void LANGUAGE plpgsql AS $body$ DECLARE result Integer; BEGIN result := a + b; INSERT INTO tbl_result(value) VALUES (result); END; $body$; |
in MySQL:
|
1 2 3 4 5 6 7 8 9 10 11 |
DELIMITER $$ CREATE PROCEDURE sp_test() BEGIN SELECT * FROM tbl1; SELECT * FROM tbl2; END $$ DELIMITER ; |
Example 4: plpgsql function with int return type
Schema SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR REPLACE FUNCTION sum(IN a Integer, IN b Integer) RETURNS int LANGUAGE plpgsql AS $$ DECLARE result Integer; BEGIN result := a + b; return result; END; $$; |
Query SQL:
|
1 2 3 |
SELECT sum(2,3); |
Outputs:
|
1 2 3 4 5 |
sum ---- 5 |
Example 5: plpgsql function with setof as a return type
Schema SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE IF NOT EXISTS tbl_employee(name varchar, state varchar); CREATE OR REPLACE FUNCTION func_get_employees() RETURNS setof tbl_employee LANGUAGE plpgsql AS $body$ BEGIN INSERT INTO tbl_employee(name, state) values ('John', 'NSW'); INSERT INTO tbl_employee(name, state) values ('Peter', 'VIC'); INSERT INTO tbl_employee(name, state) values ('SAM', 'VIC'); Return query SELECT * FROM tbl_employee; END; $body$; |
Query SQL:
|
1 2 3 |
SELECT func_get_employees(); |
Outputs:
|
1 2 3 4 5 6 7 |
func_get_employees -------------------- (John,NSW) (Peter,VIC) (SAM,VIC) |
How can you return as a table?
|
1 2 3 |
SELECT (func_get_employees()).*; |
Outputs:
|
1 2 3 4 5 6 7 |
name state ---------------- John NSW Peter VIC SAM VIC |
to select only state?
|
1 2 3 |
SELECT (func_get_employees()).state; |
Outputs:
|
1 2 3 4 5 6 7 |
state ----- NSW VIC VIC |
Example 6: plpgsql function with table as a return type
Schema SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE IF NOT EXISTS tbl_employee(name varchar, state varchar); CREATE OR REPLACE FUNCTION func_get_employees() RETURNS table (nm varchar, state varchar) LANGUAGE plpgsql AS $body$ BEGIN INSERT INTO tbl_employee(name, state) values ('John', 'NSW'); INSERT INTO tbl_employee(name, state) values ('Peter', 'VIC'); INSERT INTO tbl_employee(name, state) values ('SAM', 'VIC'); Return query SELECT * FROM tbl_employee; END; $body$; |
Query SQL:
|
1 2 3 |
SELECT func_get_employees(); |
Outputs:
|
1 2 3 4 5 6 7 |
func_get_employees -------------------- (John,NSW) (Peter,VIC) (SAM,VIC) |
Q03 What is the difference between a function using SQL as a language vs plpgsql as a language?
A03 plpgsql and plain SQL functions are both part of the toolsets, and try to use the simplest toolset possible to get the task at hand done.
1. Use views where possible.
2. Where a view is not suitable, use an SQL function.
3. Where an SQL function isn’t suitable, use plpgsql function.
4. Where plpgsql is too limited or not expressive enough, use PL/Perl, PL/Python, PL/Java, or whatever your preference is.
Example 7: function using IN and OUT parameters with SQL language
Schema SQL:
|
1 2 3 4 5 6 7 8 |
CREATE OR REPLACE FUNCTION func_dog_years(INOUT age int, OUT dog_years int) LANGUAGE sql AS $body$ SELECT age, age * 7; $body$; |
Query SQL:
|
1 2 3 |
SELECT * FROM func_dog_years(5); |
Outputs:
|
1 2 3 4 5 |
age dog_years ------------------- 5 35 |
Example 8: function using IN and OUT parameters as above with plpgsql language
Schema SQL:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE OR REPLACE FUNCTION func_dog_years(INOUT age int, OUT dog_years int) LANGUAGE plpgsql AS $body$ BEGIN dog_years = (age * 7); END $body$; |
Query SQL:
|
1 2 3 |
SELECT * FROM func_dog_years(5); |
Outputs:
|
1 2 3 4 5 |
age dog_years ------------------- 5 35 |
Example 9: function using setof with SQL language
Schema SQL:
Use tbl_employee to return a single row, and setof tbl_employee to return multiple rows.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE IF NOT EXISTS tbl_employee(name varchar, state varchar); CREATE OR REPLACE FUNCTION func_get_employees(IN loc varchar) RETURNS setof tbl_employee LANGUAGE sql AS $body$ INSERT INTO tbl_employee(name, state) values ('John', 'NSW'),('Peter', 'VIC'), ('SAM', 'VIC'); SELECT * FROM tbl_employee WHERE state = loc; $body$; |
Query SQL:
|
1 2 3 |
SELECT (func_get_employees('VIC')).*; |
Outputs:
|
1 2 3 4 5 6 |
name state --------------- Peter VIC SAM VIC |
Example 10: function using plpgsql language
Schema SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE IF NOT EXISTS tbl_employee(name varchar, state varchar); CREATE OR REPLACE FUNCTION func_get_employee_name(IN loc varchar) RETURNS varchar LANGUAGE plpgsql AS $body$ BEGIN INSERT INTO tbl_employee(name, state) values ('John', 'NSW'),('Peter', 'VIC'), ('SAM', 'SA'); RETURN name FROM tbl_employee WHERE state = loc; END $body$; |
Query SQL:
|
1 2 3 |
SELECT func_get_employee_name('VIC'); |
Outputs:
|
1 2 3 4 5 |
func_get_employee_name ------------------------ Peter |
Example 11: function using plpgsql language using INTO
Schema SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE IF NOT EXISTS tbl_employee(name varchar, state varchar); CREATE OR REPLACE FUNCTION func_get_employee_name(IN loc varchar, OUT result varchar) LANGUAGE plpgsql AS $body$ BEGIN INSERT INTO tbl_employee(name, state) values ('John', 'NSW'),('Peter', 'VIC'), ('SAM', 'SA'); SELECT name INTO result FROM tbl_employee WHERE state = loc; END $body$; |
Query SQL:
|
1 2 3 |
SELECT func_get_employee_name('VIC'); |
Outputs:
|
1 2 3 4 5 |
func_get_employee_name ------------------------- Peter |
Example 12: function using plpgsql language using INTO
Schema SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE IF NOT EXISTS tbl_employee(name varchar, state varchar); CREATE OR REPLACE FUNCTION func_get_employee_name(IN loc varchar, OUT result1 varchar, OUT result2 varchar) LANGUAGE plpgsql AS $body$ BEGIN INSERT INTO tbl_employee(name, state) values ('John', 'NSW'),('Peter', 'VIC'), ('SAM', 'SA'); SELECT name, state INTO result1, result2 FROM tbl_employee WHERE state = loc; END $body$; |
Query SQL:
|
1 2 3 |
SELECT func_get_employee_name('VIC'); |
Outputs:
|
1 2 3 4 5 |
func_get_employee_name -------------------------- (Peter,VIC) |
Example 13: function using plpgsql language with LOOP
Schema SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE OR REPLACE FUNCTION func_loop(IN max_num int) RETURNS int LANGUAGE plpgsql AS $body$ DECLARE tot_sum int DEFAULT 0; BEGIN FOR i IN REVERSE max_num .. 1 BY 2 LOOP tot_sum := tot_sum + i; END LOOP; RETURN tot_sum; END $body$; |
Query SQL:
|
1 2 3 |
SELECT func_loop(5); |
Outputs:
|
1 2 3 4 5 |
func_loop ----------- 9 |
Q04 What is a DO block in plpgsql?
A04 DO executes an anonymous code block, or in other words a transient anonymous function in a procedural language.
Example 14: plpgsql language with DO block & RAISE NOTICE
Schema SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE IF NOT EXISTS tbl_employee(name varchar, state varchar); INSERT INTO tbl_employee(name, state) values ('John', 'NSW'); INSERT INTO tbl_employee(name, state) values ('Peter', 'VIC'); INSERT INTO tbl_employee(name, state) values ('SAM', 'SA'); DO $body$ DECLARE rec record; BEGIN FOR rec IN SELECT name, state FROM tbl_employee LOOP RAISE NOTICE '%, %', rec.name, rec.state; END LOOP; END; $body$ LANGUAGE plpgsql |
Example 15: plpgsql language with DO block & FOREACH LOOP
Schema SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DO $body$ DECLARE array1 int[] := array[1,2,3]; i int; BEGIN FOREACH i IN ARRAY array1 LOOP RAISE NOTICE '%', i; END LOOP; END; $body$ LANGUAGE plpgsql |
Example 16: plpgsql language with DO block & WHILE LOOP
Schema SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DO $body$ DECLARE i int DEFAULT 1; total int DEFAULT 0; BEGIN WHILE i <= 10 LOOP total := total + 1; i := i + 1; END LOOP; RAISE NOTICE '%', total; END; $body$ LANGUAGE plpgsql |
Refer documentation to use CONTINUE and EXIT in loops.
