The function is the set of
PostgreSQL statements that stored on the database server and can be invoked
using the SQL interface. PostgreSQL function is also known as Stored Procedure
(If you are familiar with SQL Server you will be aware with Stored Procedure).
In PostgreSQL, procedural languages such as PL/pgSQL, C, Perl, Python, and Tcl
are referred to as stored procedures.
Syntax of PostgreSQL function
CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
DECLARE
declaration;
[...]
BEGIN
< function_body
>
[...]
RETURN { variable_name |
value }
END; LANGUAGE plpgsql;
|
function_name: Name of the function
[OR REPLACE]: It is optional, uses it when you
want to modify the function
RETURN: It specifies a data type that is
same as function body is returning
function_body: The function_body contains the
executable parts(PostgreSQL statements).
plpgsql: It specifies the name of the
language in which the function is implemented.
For the illustration, see the following example.
1- Create a Product
table
CREATE TABLE public.product
(
id serial NOT NULL,
brand text COLLATE pg_catalog."default",
category text COLLATE pg_catalog."default",
qty integer
)
|
2- Insert some records
INSERT INTO product(brand,category,qty)
VALUES('Arrow','Cloths',3000);
INSERT INTO product(brand,category,qty)
VALUES('Samsung','Mobile',4500);
INSERT INTO product(brand,category,qty)
VALUES('iPad','Tablet',2000);
INSERT INTO product(brand,category,qty)
VALUES('Prestige','Kitchen',200);
|
3- Create a function
that will return the total product quantity
CREATE OR REPLACE FUNCTION
totalProductQuantity()
RETURNS integer AS $totalQty$
declare
sumQty integer;
BEGIN
SELECT SUM(Qty) into sumQty FROM Product;
RETURN sumQty;
END;
$totalQty$ LANGUAGE plpgsql;
|
4- If you got the
following message then your function is ready to invoke using the SQL
interface.
Success Message:
CREATE FUNCTION
Query returned successfully in 117 msec.
|
5- Invoke the function
as below and see the result:
select * from totalProductQuantity();
|
Result:
totalProductQuantity
970
5- If you are using
pgAdmin then you can see under the functions "totalProductQuantity"
Example of a function as a return table type
1- I want to result
set of all records using function, then you can see the following example.
CREATE OR REPLACE FUNCTION
GetAllProducts()
RETURNS TABLE(prod_id integer, brand text, category text, qty integer)
AS $BODY$
BEGIN
RETURN QUERY SELECT p.id, p.brand, p.category, p.qty FROM Product p;
END;
$BODY$ LANGUAGE plpgsql;
|
2- Execute the
function
select * from GetAllProducts();
|
Result:
Example of a parameterized function
1- I want to pass the parameter in function and result set accordingly, suppose I want to search
product on the Brand basis.
CREATE OR REPLACE FUNCTION
SearchByBrand(brandname text)
RETURNS TABLE(prod_id integer, brand text, category text, qty integer)
AS $BODY$
BEGIN
RETURN QUERY SELECT p.id, p.brand, p.category, p.qty FROM Product p
WHERE p.brand like'%'||brandname||'%';
END;
$BODY$ LANGUAGE plpgsql;
|
2- Now, run the
function as below
select * from SearchByBrand('Arrow');
|
Result:
