PostgreSQL - SPLessons

PostgreSQL Functions

Chapter 13

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PostgreSQL Functions

PostgreSQL Functions

shape 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.

shape 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.

shape Examples

By viewing the below example the concept of PostgreSQL Functions can be easily understood.

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)

Summary

shape Key Points

  • PostgreSQL Functions – Is a type of command which accepts ‘n’ number 0f values as input and return a single values.