PostgreSQL mini cookbook: Automating processes

PostgreSQL mini cookbook: Automating processes, those tricks back to 2001 and still works today. 😉

Last Update: January 14, 2022

Enabling `plpgsql`

You want to write functions and build triggers in Postgres’ scripting language plpgsql.

PL/pgSQL is a loadable procedural language for the PostgreSQL database system. The design goals of PL/pgSQL were to create a loadable procedural language that

can be used to create functions and trigger procedures,
adds control structures to the SQL language,
can perform complex computations,
inherits all user-defined types, functions, and operators,
can be defined to be trusted by the server,
is easy to use.

Functions created with PL/pgSQL can be used anywhere that built-in functions could be used. For example, it is possible to create complex conditional computation functions and later use them to define operators or use them in index expressions.

In PostgreSQL 9.0 and later, PL/pgSQL is installed by default.

Solution

As superuser (postgres) use the command-line tool createlang .

So to activate plpgsql in database db1 use:

$ createlang plpgsql db1

Recording a log of changes to a table

You want to keep a log of changes made to certain tables in a separate log-table.

Solution

Use one or more triggers to trap the changes and write messages to the log table.

Discussion

In the example database there is a diary table that lets users record notes against a company.

It would be useful if when an order is taken, the fact is recorded in the diary against that company. We’ll want to trap inserts to the orders table and build an appropriate message.

CREATE FUNCTION log_new_orders() RETURNS opaque AS '
DECLARE
  product_desc text;
  msg text;
BEGIN
  SELECT INTO product_desc pr_desc
  FROM products
  WHERE pr_id = NEW.ord_product;
  msg := ''New order: '' || NEW.ord_qty || '' items of '' || product_desc;
  INSERT INTO DIARY (dy_company,dy_type,dy_notes)
    VALUES (NEW.ord_company,''ORDER'',msg);
  RETURN NEW;
END;
' language 'plpgsql';

CREATE TRIGGER order_new_log_trig
  AFTER INSERT ON orders FOR EACH ROW
    EXECUTE PROCEDURE log_new_orders();

Note: the doubling of quotes in the body of the function - they are two single quotes (’) not one double (").

Updating a “last changed” field

You want a “last changed” timestamp on a table that gets automatically updated every time a user inserts/updates a record.

Solution

Define update/insert triggers on the table in question.

Discussion

To automatically update co_lastchg every time a company record is inserted or updated use:

CREATE FUNCTION set_co_lastchg()
RETURNS opaque AS '
BEGIN
  NEW.co_lastchg = now();
  RETURN NEW;
END;
' language 'plpgsql';

CREATE TRIGGER co_lastchg_trig
  BEFORE INSERT OR UPDATE ON companies FOR EACH ROW
    EXECUTE PROCEDURE set_co_lastchg();