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)
RETURNS data_type
LANGUAGE language_name AS
$function$
--function body
$function
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)
RETURNS integer
LANGUAGE sql
AS $function
$SELECT salary FROM employee WHERE employee_id = id;
$function$
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:
select increment(100);
CREATE OR REPLACE FUNCTION public.increment(i integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN i + 1;
END;
$function$
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.
