PostgreSQL mini cookbook: Automating processes
You want to write functions and build triggers
in Postgres' scripting language
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.
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.
Use one or more triggers to trap the changes and write messages to the log table.
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.
Define update/insert triggers on the table in question.
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();
You can repeat this for every timestamp to be updated.
- PostgreSQL SQL Create Table
- PostgreSQL SQL Create Trigger
- PostgreSQL PL/pgSQL - SQL Procedural Language
Encrypted your DNS to protect your privacy and firewall to block phishing, malicious domains, block ads in all browsers and apps