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:



