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 body

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
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:

select increment(100);

CREATE OR REPLACE FUNCTION public.increment(i integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
      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.