Introduction to PostgreSQL Functions
PostgreSQL functions is also called as a stored procedure in PostgreSQL, PostgreSQL stored procedure or function is set in SQL statements. Create statement is used in it to create new function, we can create a PostgreSQL function in many languages like C, python, SQL and PL/pgsql. Function is very important in PostgreSQL, before PostgreSQL version 11 function will act as a procedure in PostgreSQL database, after PostgreSQL version 11 function and procedure will be differentiated. Its function name will be unique that means it will not be same as other name of function which was already created in database.
Syntax:
Below given is the syntax:
CREATE [OR REPLACE (Replace the function if already exist)] FUNCTION
name (Name of function) ( [ [ argmode (Mode of argument)] [ argname(Name of argument) ] argtype (Type of argument)[ { DEFAULT | = } default_expr (Default expression of function) ] [, ...] ] )
[RETURNS rettype (Return type of functions)
| RETURNS TABLE (Table name which we have used to return from function)(column_name (Name of column) column_type (Data type of column)[, ...] ) ]
{LANGUAGE lang_name (Language name which was used to create function)
| WINDOW (Window function)
| IMMUTABLE | STABLE | VOLATILE (Attribute of function)
| COST execution_cost (Execution costs)
| ROWS result_rows (No of rows returned from function)
| SET configuration_parameter (Specified configuration parameter) {TO value | = value | FROM CURRENT}
| AS 'definition' (Definition of function)
} ...
[WITH (attribute [, ...] ) ] (Display optional function information)
Below is the parameter description of above syntax:
- Create: Creates a new function.
- Function name: Name of function which we are creating. Function name should be unique.
- Replace: Replace the function name which is already present in database.
- Argmode: Mode of argument which we have used in function, default argument mode of function is IN.
- Argname: Name of argument which we have used to create a function.
- Argtype: Type of argument which we have used to create a function.
- Default: Default states that if we not given any argument it will take default.
- Rettype: This defines as a return type of functions. The return type can be anything composite, base or table column.
- Table: Table name which column we have return from function.
- Column name: This defines that the name of columns which we have return in return table syntax.
- Column type: This defines as a data type of column which we have return in return table syntax.
- Language name: This defines as name of language which we have used to create function in it. The language can be a python, pl/pgsql or C.
- Window: This defines as we are creating a window function instead of plain function.
- Execution cost: This parameter defines as the total time required to execute the function, it shows positive number.
- Cost: Cost parameter define execution cost of function.
- Rows: Rows parameter defines as total rows returned from function.
- Result rows: Result of rows which have return from function.
- Configuration parameter: This defines as configuration parameter set as different value.
- Definition: This parameter defines as definition of function.
- Attribute: This will display extra information of a function.
How PostgreSQL Functions works?
- PostgreSQL functions is also called as a stored procedure or stored function in PostgreSQL.
- PostgreSQL stored procedure or function is set in SQL statements or in PL/SQL statements.
- Its name will be unique means it will not be same as other name of function which have already created in database.
- Create statement is used in function to create new function also we can replace the existing function using replace keyword at the time of new function creation.
- We create a new function in many languages like C, python, SQL and PL/pgsql. This languages is used to create new function in it.
- It is very important, before version 11 function will act as procedure in PostgreSQL after PostgreSQL version 11 function and procedure will be differentiated.
- It is nothing but a set of SQL statements that was stored on database server and invokes after using a SQL interface.
- It is used to execute several query in one function and return result as same that was written in function.
- We can check all function from database by using a “\df”. This command is used to display all function from database.
- We can implement it in different languages. If we not define the language at the time of function creation it will choose default language.
- It uses interface that defines an argument and return type of function as we have stated in syntax of function.
Examples of PostgreSQL Functions
Given below is the example to create, modify and drop a function:
Example #1: Create PostgreSQL function
Below is the example of create new function.
Code:
CREATE OR REPLACE FUNCTION Func_Test ()
RETURNS integer AS $test$
declare
test integer;
BEGIN
SELECT count (1) into test FROM EMP;
RETURN test;
END;
$test$ LANGUAGE plpgsql;
\df
Output:
In above example we have created a function name as Func_Test. We have used pl/pgsql language to create a new function.
We have count record from EMP table. We can call or execute Func_Test by using following command are as follows.
Code:
select Func_Test();
\df+
Output:
Example #2: Modify PostgreSQL function
We are changing owner of Func_Test function from postgres to test user.
Code:
ALTER FUNCTION Func_Test OWNER to test;
\df+
Output:
Example #3: Drop PostgreSQL function
We can drop by using drop command. We are dropping Func_Test function.
Code:
drop function Func_Test;
\df+
Output:
Conclusion
PostgreSQL function or stored procedure is nothing but set of SQL statements that was stored on database server and invokes after using a SQL interface. Create statement is used to create a new function in it. We can implement this function in any languages like C, Python and pl/pgsql.
Recommended Articles
This is a guide to PostgreSQL Functions. Here we discuss the introduction to PostgreSQL functions along with how does it works and respective examples. You may also have a look at the following articles to learn more –
