PostgreSQL Create Function Examples
PostgreSQL stored functions can be created by connecting to a PostgreSQL database and executing a create function statement. Below is the syntax for the create function statement:
CREATE OR REPLACE FUNCTION function_name(param1 data_type, param2 data_type)
LANGUAGE language_name AS
The variables that need filled in by the user are the following:
1. function_name: Enter the name of the function here, for example, get_salary
2. parameter_list: The parameter list contains the name of the parameter and the data type of the parameter. Here is an example parameter list:
i1 integer, d1 date
3. Returns Data Type: The type of data that is returned needs to be specified, for example, int, date, etc.
4. language_name: Built-in languages supported by PostgreSQL are sql and plpgsql. If the Python language pack is installed in the database, the Python language is also supported using the plpythonu language name.
5. function_body: The function body contains the commands to execute. The commands are written in the language specified by the language_name, for example, SQL.
The following example function named get_salary has one parameter and returns an integer.
The parameter is the employee id. This function returns the salary for the employee
with the passed in employee id. This function is written using the SQL language.
To call the function below, a simple SQL select statement can be executed. Here is an example:
select get_salary (8323435);
CREATE OR REPLACE FUNCTION public.get_salary(id int)
AS $function $SELECT salary FROM employee WHERE employee_id = id;
The following create function example creates a function named increment. This function takes
the passed in integer and adds 1 to it. This function is written in the PostgreSQL procedural language
plpgsql. This function can be called using the following syntax:
CREATE OR REPLACE FUNCTION public.increment(i integer)
RETURN i + 1;
If you are looking for a tool that provides the capability to edit, create, call, and view PostgreSQL
stored functions, please check out RazorSQL. It has a free, full-featured 30-day trial and can be downloaded
from the following: RazorSQL Download.