The PL/pgSQL Language

Rdbhost now supports using the language PL/pgSQL in queries. The language is used to define functions (aka procedures) that can be called from SQL, just as a native procedure can.

General Merits

PL/pgsql programming allows control flow more sophisticated than that allowed by plain SQL. Later statements can depend on the outcome of earlier statements, coding with conditional blocks. The set of queries and conditional blocks can be requested of the server in one request, reducing the time required for the sequence to execute.

From the Postgresql.org website, the design goals of PL/pgSQL include:

  • 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.

Usage Example

Programmers with experience in MySQL might want an equivalent to the REPLACE INTO statement. In PostgreSQL, you can use the sequence of i) UPDATE, ii) catch exception, iii)INSERT, like:

This example uses exception handling to perform either UPDATE or INSERT, as appropriate: CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN LOOP -- first try to update the key UPDATE db SET b = data WHERE a = key; IF found THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END; END LOOP; END; $$ LANGUAGE plpgsql; SELECT merge_db(1, 'david'); SELECT merge_db(1, 'dennis');

This code segment taken from the PostgreSQL developer web site.