PostgreSQL capacities, otherwise called Stored Procedures, permit you to do tasks that would regularly go on a few inquiries and round outings in a solitary capacity inside the information base. Capacities permit data set reuse as different applications can collaborate straightforwardly with your put away techniques rather than a center level or copying code.
Capacities can be made in a language of your decision like SQL, PL/pgSQL, C, Python, and so forth
Syntax
The essential language structure to make a capacity is as per the following −
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;
Where,
- work name indicates the name of the capacity.
- [OR REPLACE] choice permits changing a current capacity.
- The capacity should contain a bring explanation back.
- RETURN condition indicates that information type you will get back from the capacity. The return_datatype can be a base, composite, or space type, or can reference the sort of a table segment.
- work body contains the executable part.
- The AS catchphrase is utilized for making an independent capacity.
- plpgsql is the name of the language that the capacity is executed in. Here, we utilize this choice for PostgreSQL, it tends to be SQL, C, inner, or the name of a client characterized procedural language. For in reverse similarity, the name can be encased by single statements.
Example
The accompanying model outlines making and calling an independent capacity. This capacity restores the complete number of records in the COMPANY table. We will utilize the COMPANY table, which has the accompanying records −
testdb# select * from COMPANY;
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
(7 rows)
Capacity totalRecords() is as per the following −
CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
SELECT count(*) into total FROM COMPANY;
RETURN total;
END;
$total$ LANGUAGE plpgsql;
At the point when the above question is executed, the outcome would be −
testdb# CREATE FUNCTION
Presently, let us execute a call to this capacity and check the records in the COMPANY table
testdb=# select totalRecords();
At the point when the above inquiry is executed, the outcome would be −
totalrecords
--------------
7
(1 row)