PostgreSQL Stored Procedures and Functions Notes¶

Source: Everything you need to know about Postgres stored procedures and functions

History¶

PostgreSQL began implementing Stored Procedure capabilities with the release of version 11. Before version 11, to declare a stored procedure one would simply write a User Defined Function, or udf, with a return type of void:

CREATE FUNCTION function_name() RETURNS void
LANGUAGE SQL
AS $$
    UPDATE a_table SET a_column = false WHERE balance < 0;
$$;

and to call it use SELECT:

SELECT function_name();

which would return a NULL void type result.

Beginning with version 11, one can now declare a PROCEDURE:

CREATE PROCDURE my_stored_procedure()
LANGUAGE SQL
AS $$
    UPDATE accounts SET active = false WHERE balance < 0;
$$;

and call using CALL:

CALL my_stored_procedure();

This stored procedure does not return any result, although will show any errors that occurred during its invocation.

Function¶

The syntax for declaring a PostgreSQL Function is the same for all versions of PostgreSQL:

CREATE OR REPLACE FUNCTION account_type_count(account_type text) RETURNS integer
LANGUAGE plpgsql
AS $$
    DECLARE account_count int;
BEGIN

    SELECT COUNT(*) INTO account_count
    FROM accounts
    WHERE accounts.account_type = $1;

    RETURN account_count;

END;
$$;

From these examples, you can see that you can use functions to update and retrieve data, or to just perform a procedure, but now we also have procedures, which are designed specifically with performing a set of actions in mind.

Functions come in even more flavors though. We have the regular kind, which are used in queries, but we also have some additional ones, which we’ll get to shortly.

Parameters¶

Function parameters can be referenced using either positional references or named references. Positional references work by using $1 to reference the first parameter, then $2 for the second, and so on. For named references, you can just use the name of the parameter. So if you had a parameter called my_value, you could just reference it using either method:

SELECT $1 + 1;
SELECT my_value + 1;

This is true for functions written in SQL or PL/pgSQL languages. You can also call the function and specifically set the parameters in whichever order you like using named notation:

SELECT create_user(age => 50, country => 'France', username => 'Henri');

You can set the default value for a parameter if the user doesn’t specify it using the DEFAULT keyword and a value of your choosing:

CREATE FUNCTION create_user(username text, country text, age int, active bool DEFAULT true)...

This means we could use named notation, not specifying the “active” parameter, and it would automatically set that value to “true”.

There is also the OUT parameter, which is a way to produce an output that returns those fields in the result. And INOUT, which we use to define a parameter that accepts input, but will also be included in the output.

CREATE FUNCTION dog_years(INOUT age int, OUT dog_years int)
LANGUAGE SQL
AS $$
  SELECT age, age * 7;
$$;

SELECT * FROM dog_years(8);
 age | dog_years

-----+-----------
   8 |    56
(1 row)

You may also have a function where you want the user to be able to specify multiple items, but you don’t know how many to expect because the number can vary. This is when you can use the VARIADIC parameter, which must always be the last parameter defined, and you define it as an array type:

CREATE FUNCTION create_post(title text, content text, VARIADIC tags text[])

RETURNS integer

LANGUAGE SQL AS $$

  INSERT INTO posts (title, content, tags)

  VALUES (title, content, array_to_string(tags,','))

  RETURNING id;

$$;

Aggregate Functions¶

Aggregate functions are functions used by aggregates in order to reach the result required. In short, you would only need such functions if you’re creating your own custom aggregates.

Trigger Functions¶

Trigger functions don’t contain any code themselves, but instead call functions when the trigger’s conditions are met. Trigger functions only return a type of trigger.

For example, we’ll create a function that logs information when a table is changed.

We have a table of user information:

CREATE TABLE users (

  id serial PRIMARY KEY,

  username text NOT NULL,

  display_name text NOT NULL,

  tag_line text NULL

);

Then a second table to log the values in that table for when they change:

CREATE TABLE user_change_log (entry_time timestamp with time zone, entry users);

Now we’ll create a function that inserts changed rows into the user_change_log table:

CREATE FUNCTION log_user_change() RETURNS trigger

LANGUAGE plpgsql AS

$$

BEGIN

  IF NEW <> OLD THEN

INSERT INTO user_change_log(entry_time, entry) VALUES (now(), NEW);

  END IF;

  RETURN NEW;

END;

$$;

Finally, we’ll create a trigger on the “users” table to execute that function when updates are made to the table:

CREATE TRIGGER user_changes

  AFTER UPDATE ON users

  FOR EACH ROW

  EXECUTE PROCEDURE log_user_change();

There are two other types of trigger that use functions too: constraint triggers and event triggers. Constraint triggers work in much the same way as we’ve just demonstrated, but have additional properties. Event triggers must use functions that return a type of event_trigger, and are fired when certain commands are fired. These functions have additional values associated with the event available.

Other function features¶

Functions can have many more options, including whether it’s a WINDOW function (only currently useful for C language functions), whether it’s parallel-safe, the cost of running the function for the executor to bear in mind, the configuration parameters specifically for this function (using SET to set), and various others.

Appendix: Links¶

Backlinks:

list from [[PostgreSQL Stored Procedures Notes]] AND -"Changelog"