User-defined functions and procedures

Eugenia Kuzina

Contents

Overview
- User-defined functions
- User-defined procedures
Query language (SQL) functions
Procedural language functions

Overview

User-defined PostgreSQL functions and procedures are designated to perform custom tasks. They are usually used to handle specific scenarios.

User-defined functions

ADPG/PostgreSQL provides the following types of functions:

Query language functions — functions written in SQL. This type of function is described below in greater detail.
Procedural language functions — functions written in other languages besides SQL and C. These other languages are generically called procedural languages (PL), for example, PL/pgSQL, PL/Tcl. Procedural languages are not built into the ADPG/PostgreSQL server, they are offered by loadable modules.
Internal functions — functions written in C that are statically linked to the ADPG/PostgreSQL server. All internal functions are declared during the initialization of the database cluster. Users can use CREATE FUNCTION to create an additional alias name for an internal function. See Internal functions.
C-language functions — functions written in C. These functions are compiled into dynamically loadable objects (also called shared libraries) and are loaded by the server on demand. Dynamic loading distinguishes C-language functions from internal functions. The actual coding conventions are essentially the same for both. See C-language functions.

Every type of function can take base and composite types as arguments (parameters). Also, functions can return a base type, composite type, or a set of base and composite values.

You can use the CREATE FUNCTION command to define a new function, ALTER FUNCTION to change the definition of a function, and DROP FUNCTION to remove a function. DROP FUNCTION requires specifying the argument types, since several functions can exist with the same name and different argument lists. See Function overloading.

User-defined procedures

A procedure is a database object similar to a function. The key differences are:

Procedures are defined with the CREATE PROCEDURE command.
Procedures do not return a value, therefore CREATE PROCEDURE does not contain the RETURNS clause. However, procedures can return data via output parameters.
A function is called as part of a query or DML command, a procedure is called in isolation using the CALL command.
A procedure can commit or roll back transactions during its execution (then automatically start a new transaction) if the invoking CALL command is not included in an explicit transaction block.
Some function attributes (for example, STRICT) cannot be applied to procedures. These attributes affect function calls in queries and are not relevant to procedures.

The sections below describe how to define functions, which also applies to procedures, taking into account the above considerations.

Functions and procedures are also known as routines. ADPG/PostgreSQL provides the ALTER ROUTINE and DROP ROUTINE commands that can operate on functions and procedures without specifying the exact type of object. Note, that there is no CREATE ROUTINE command.

Query language (SQL) functions

SQL functions execute an arbitrary list of SQL statements and return the result of the last query in the list. Use the CREATE FUNCTION command to define a function.

In the simple case, the first row of the last query’s result will be returned. Remember that the first row of a multi-row result is only precisely defined if you use ORDER BY. If the last query returns no rows, the NULL value will be returned.

You can also declare an SQL function that returns a set (multiple rows). To do this, specify the function’s return type as SETOF <type> or declare it as RETURNS TABLE(<columns>). In this case, all rows of the last query’s result are returned.

The body of an SQL function must be a list of SQL statements separated by semicolons. A semicolon after the last statement is optional. SQL statements can include the SELECT queries, data modification queries (INSERT, UPDATE, and DELETE), and other SQL commands. You cannot use the transaction control commands (for example, COMMIT, SAVEPOINT) and some utility commands (for example, VACUUM) in SQL functions. The last statement must be SELECT or INSERT, UPDATE, or DELETE commands that have a RETURNING clause.

It is also possible to create an SQL function that performs actions but does not return a value. To do this, define a function as returning void. For example, the function below removes rows where the value of quantity is 0 from the books table:

CREATE FUNCTION delete_books() RETURNS void AS $$
    DELETE FROM books
        WHERE quantity = 0;
$$ LANGUAGE SQL;

SELECT delete_books();

You can also execute this code as a procedure:

CREATE PROCEDURE delete_books_proc() AS $$
    DELETE FROM books
        WHERE quantity = 0;
$$ LANGUAGE SQL;

CALL delete_books_proc();

The syntax of the CREATE FUNCTION and CREATE PROCEDURE commands requires the function body to be written as a string constant. It is usually most convenient to use dollar quoting (see Dollar-quoted string constants) for the string constant. If you choose to use regular single-quoted string constant syntax, you must double the single quote marks (') and utilize backslashes (\) to escape symbols in the body of the function (see String constants).

NOTE

PostgreSQL parses the function body before the execution. If an SQL function contains commands that alter the system catalogs (for example, CREATE TABLE), the effects of these commands will not be visible during parse analysis of later commands in the function. So, the expressions CREATE TABLE table1 (…); INSERT INTO table1 VALUES(…); packaged into a single SQL function do not work as expected because table1 does not exist when parsing the INSERT command. It is recommended to use PL/pgSQL functions instead of SQL functions in these types of scenarios.

SQL functions on base types

The simplest SQL function has no arguments and returns a base type:

CREATE FUNCTION my_function1() RETURNS integer AS $$
    SELECT 1;
$$ LANGUAGE SQL;

SELECT my_function();

The result:

my_function
-------------
           1

The following SQL function takes base types as arguments:

CREATE FUNCTION my_calc(a integer, b integer) RETURNS integer AS $$
    SELECT a * b;
$$ LANGUAGE SQL;

SELECT my_calc(15, 13) AS answer;

The result:

answer
--------
    195

Arguments of an SQL function can be referenced in the function body using either names or numbers. The function above can be rewritten without argument names:

CREATE FUNCTION my_calc1(integer, integer) RETURNS integer AS $$
    SELECT $1 * $2;
$$ LANGUAGE SQL;

The function below performs calculations and returns the updated balance as the result of the last SELECT command:

CREATE FUNCTION function1(account_id integer, income numeric) RETURNS numeric AS $$
    UPDATE accounts
        SET balance = balance + income
        WHERE account_id = function1.account_id;
    SELECT balance FROM accounts WHERE account_id = function1.account_id;
$$ LANGUAGE SQL;

In this example, the name of the first argument is account_id and the name of a table column is the same — account_id. To refer to the argument in the UPDATE command, use the argument name with the function name — function1.account_id. Alternatively, you can specify a different argument name.

The function1 function can be rewritten with the RETURNING clause:

CREATE FUNCTION function1(account_id integer, income numeric) RETURNS numeric AS $$
    UPDATE accounts
        SET balance = balance + income
        WHERE account_id = function1.account_id
    RETURNING balance;
$$ LANGUAGE SQL;

NOTE

If the final SELECT or RETURNING clause in an SQL function does not return the function’s declared result type, PostgreSQL will cast the value to the required type if that is possible with an implicit or assignment cast. Otherwise, you must explicitly cast the result. For more information, see Type Conversion and CREATE CAST.

Functions can be declared with default values for some or all of their input arguments. Default values are used when a function is called with an insufficient number of arguments. Since arguments can only be omitted from the end of the argument list, all parameters after the parameter with a default value will also receive their default values. For example:

CREATE FUNCTION function_sum (a int, b int DEFAULT 10, c int DEFAULT 20)
RETURNS int  AS $$
    SELECT a + b + c;
$$ LANGUAGE SQL;

SELECT function_sum(1, 2, 3);

The result is 6.

You can call it with a single argument 5:

SELECT function_sum(5);

The result is 35.

SQL functions on composite types

In functions with arguments of composite types, it is necessary to specify not only the argument but also which attribute (field) of this argument should be used.

For example, create the following table:

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author_id INT NOT NULL,
  price NUMERIC);

INSERT INTO books (title, author_id, price) VALUES
('Mrs. Dalloway',1,50),
('To the Lighthouse',1,61),
('To Kill a Mockingbird',2,48);

Define a function that adds the tax to a price:

CREATE FUNCTION add_tax(books) RETURNS numeric AS $$
    SELECT $1.price * 1.2;
$$ LANGUAGE SQL;

SELECT title, add_tax(books.*) AS price_with_tax
    FROM book1;

The result:

        title         | price_with_tax
-----------------------+----------------
 Mrs. Dalloway         |           60.0
 To the Lighthouse     |           73.2
 To Kill a Mockingbird |           57.6
(3 rows)

The required field of the argument table is specified after dot (.) — $1.price. To select the entire row of a table as a composite value, the asterisk (*) is used — books.*. See Using composite types in queries.

SQL functions with output parameters

An alternative way of describing a function results is to define it with output parameters. Output parameters provide a convenient way of creating functions that return several columns. For example:

CREATE FUNCTION function_calc (a int, b int, OUT sum int, OUT product int) AS $$
    SELECT a + b, a * b;
$$ LANGUAGE SQL;

 SELECT * FROM function_calc(5,6);

The result:

sum | product
-----+---------
  11 |      30

Note, that the code above creates an anonymous composite type for the function’s return value. It has the same result as the following code:

CREATE TYPE my_type AS (sum int, product int);

CREATE FUNCTION function_calc (int, int) RETURNS my_type AS $$
SELECT $1 + $2, $1 * $2;
$$ LANGUAGE SQL;

Output parameters are not included in the calling argument list. PostgreSQL considers only the input parameters to define the function’s calling signature. That means also that only the input parameters matter when referencing the function for purposes such as dropping it. We could drop the above function with one of the following commands:

DROP FUNCTION function_calc (a int, b int, OUT sum int, OUT product int);
DROP FUNCTION function_calc (int, int);

SQL function parameters can be marked as follows:

INT — input parameter, a part of the calling argument list.
OUT — output parameter, a part of the result type.
INOUT — parameter serves as both an input parameter (part of the calling argument list) and an output parameter (part of the result type).
VARIADIC — specifies that the function can accept variable numbers of arguments. See SQL functions with variable numbers of arguments.

SQL procedures with output parameters

Output parameters are also supported in procedures, but they work a bit differently. Output parameters must be included in the argument list in the CALL command. For example, function1 from the example above can be rewritten as follows:

CREATE PROCEDURE proc1(account_id integer, income numeric, OUT new_balance numeric) AS $$
    UPDATE accounts
        SET balance = balance + income
        WHERE account_id = proc1.account_id
    RETURNING balance;
$$ LANGUAGE SQL;

To call this procedure, you must supply an argument corresponding to the OUT parameter. Typically, NULL is used:

CALL proc1(2, 100.0, NULL);

SQL functions with variable numbers of arguments

SQL functions can be declared to accept a variable number of arguments. All optional arguments must be of the same data type. The optional arguments are passed to the function as an array. To do this, declare the last parameter as an array type and mark it as VARIADIC. For example, the following function finds the maximum of an arbitrary number of arguments:

CREATE FUNCTION max_value(VARIADIC arr numeric[]) RETURNS numeric AS $$
    SELECT max(arr[i]) FROM generate_subscripts(arr, 1) g(i);
$$ LANGUAGE SQL;

SELECT max_value(16,7,5,4,67);

The result:

max_value
----------
67

SQL functions returning sets

When an SQL function returns SETOF <type>, each row of its output is returned as an element of the result set. It is often used in the FROM clause. For example:

CREATE TABLE employees (id int, report_to int, name text);
INSERT INTO employees VALUES (1, 0, 'Joe');
INSERT INTO employees VALUES (2, 1, 'Ed');
INSERT INTO employees VALUES (3, 1, 'Mary');
INSERT INTO employees VALUES (4, 2, 'Sara');
INSERT INTO employees VALUES (5, 4, 'Ivan');

CREATE FUNCTION get_employees(int) RETURNS SETOF employees AS $$
    SELECT * FROM employees WHERE report_to = $1;
$$ LANGUAGE SQL;

SELECT * FROM get_employees(1);

The result:

 id | report_to | name
----+-----------+------
  2 |         1 | Ed
  3 |         1 | Mary

It is frequently useful to construct a query’s result by invoking a set-returning function multiple times, with the parameters for each invocation coming from successive rows of a table or subquery. Use the LATERAL key word for this. For example, consider the following tree table:

  name  | parent
--------+--------
 Parent |
 Child1 | Parent
 Child2 | Parent
 Child3 | Parent
 Child4 | Child1
 Child5 | Child1

Create a function that selects children:

CREATE FUNCTION get_children(text) RETURNS SETOF text AS $$
    SELECT name FROM tree WHERE parent = $1
$$ LANGUAGE SQL STABLE;

Execute the following query:

SELECT name, child FROM tree, LATERAL get_children(name) AS child;

The result:

  name  | child
--------+--------
 Parent | Child3
 Parent | Child2
 Parent | Child1
 Child1 | Child5
 Child1 | Child4

NOTE

If the last command of a function is INSERT, UPDATE, or DELETE with RETURNING, the command will be executed to completion, even if the function is not declared with SETOF<type>. Any extra rows produced by the RETURNING clause are skipped, but all the table modifications are made.

SQL functions returning a table

There is another way to declare a function as returning a set. You can use the RETURNS TABLE(<columns>) clause. This is equivalent to using one or more OUT parameters and marking the function as returning SETOF <type>. For example:

CREATE TABLE my_table (x int, y int);
INSERT INTO my_table VALUES (1, 11), (2, 12), (3, 13), (4, 14);

CREATE FUNCTION function_calc(a int) RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + my_table.x, $1 * my_table.y FROM my_table;
$$ LANGUAGE SQL;

SELECT * FROM function_calc(5);

The result:

 sum | product
-----+---------
   6 |      55
   7 |      60
   8 |      65
   9 |      70

Procedural language functions

The standard ADPG/PostgreSQL distribution includes four procedural languages: PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python.

Consider a function written in PL/pgSQL. The PL/pgSQL function is also defined by executing the CREATE FUNCTION command:

CREATE FUNCTION my_function(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;

PL/pgSQL is a block-structured language. The complete text of a function body must be a block. A block is defined as:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

A label is only needed if you want to identify the block for use in an EXIT statement or to qualify the names of the variables declared in the block. All variables used in a block must be declared in the DECLARE section of the block. See Declarations.

The following function defines the passed variable and stores into it the result of the password checking:

CREATE FUNCTION check_password(uname TEXT, pass TEXT) RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
    SELECT  (pwd = $2) INTO passed
    FROM    pwds
    WHERE   username = $1;
       RETURN passed;
END;
$$  LANGUAGE plpgsql

As in SQL functions, arguments can be referenced by their numbers.

For more information on PL/pgSQL functions, see PL/pgSQL — SQL Procedural Language.

Found a mistake? Seleсt text and press Ctrl+Enter to report it