Description
PostgreSQL Function is a type of command which accepts 'n' number of values as input and return a single value. The PostgreSQL Functions in PostgreSQL, where also called as Stored Procedures, that permit the following operations that would regularly take a few inquiries and rotates in a solitary capacity inside the database. PostgreSQL Functions permit the database to utilize various applications and can communicate specifically with the stored procedural methods rather than of a middle level.
Syntax
The syntax for PostgreSQL Function is as follows:
CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
DECLARE
declaration;
[...]
BEGIN
[...]
RETURN { variable_name | value }
END; LANGUAGE plpgsql;
Function-name => Specifies the name of the functions.
Return => Specifics the data type in the functions.
Function-body => Contains the executable part.
Plpgsql => Is a language in the database.
Examples
By viewing the below example the concept of PostgreSQL Functions can be easily understood.
[c]
SQLDB=# select * from employee01;
+--------+-------+-------+--------+
| emp_id | ename | sal | deptno |
+--------+-------+-------+--------+
| 1001 | mike | 12000 | 10 |
| 1002 | rambo | 13000 | 20 |
| 1003 | kate | 14000 | 10 |
| 1004 | jeo | 14000 | 20 |
| 1005 | finn | 14000 | 30 |
+--------+-------+-------+--------+
(5 rows)
SQLDB=# CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
SELECT count(*) into total FROM employee01;
RETURN total;
END;
$total$ LANGUAGE plpgsql;
SQLDB=# CREATE FUNCTION
SQLDB=# select totalRecords();
totalrecords
--------------
5
(1 row)
[/c]
Key Points
- PostgreSQL Functions - Is a type of command which accepts 'n' number 0f values as input and return a single values.