Advanced SQL

Advanced SQL

PL/pgSQL

DB9 Difference: PL/pgSQL limitations

DB9 supports PL/pgSQL with variable declarations, IF/ELSIF, FOR loops, PERFORM, RAISE, and RETURN. The following are not supported: EXECUTE (dynamic SQL), BEGIN ... EXCEPTION blocks (error handling), WHILE loops, and cursor operations. Migrate stored procedures that use these features before switching to DB9.

DB9 supports PL/pgSQL functions and procedures with variable declarations and standard control flow. EXECUTE, BEGIN ... EXCEPTION blocks, and WHILE loops are not currently supported.

SQL

CREATE FUNCTION increment(val INT) RETURNS INT AS $$
BEGIN
  RETURN val + 1;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION safe_divide(a NUMERIC, b NUMERIC) RETURNS NUMERIC AS $$
BEGIN
  RETURN a / NULLIF(b, 0);
END;
$$ LANGUAGE plpgsql;

RETURNING … INTO

DML statements inside PL/pgSQL functions can capture returned values into variables using RETURNING ... INTO. Works with INSERT, UPDATE, and DELETE.

SQL

CREATE FUNCTION create_order(p_item TEXT) RETURNS INT AS $$
DECLARE
  new_id INT;
BEGIN
  INSERT INTO orders (item) VALUES (p_item) RETURNING id INTO new_id;
  RETURN new_id;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION archive_user(p_id INT) RETURNS TEXT AS $$
DECLARE
  removed_name TEXT;
BEGIN
  DELETE FROM users WHERE id = p_id RETURNING name INTO removed_name;
  RETURN COALESCE(removed_name, 'not found');
END;
$$ LANGUAGE plpgsql;

Multiple columns can be captured into separate variables:

SQL

RETURNING id, name INTO v_id, v_name;

If the DML statement affects zero rows, the target variables are set to NULL. Expressions (not just column names) are supported in the RETURNING list. Following PostgreSQL semantics, RETURNING ... INTO with a statement that returns more than one row will capture the first row.

Triggers

Row-level and statement-level triggers on INSERT, UPDATE, and DELETE.

SQL

CREATE FUNCTION audit_trigger() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP, NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
  AFTER INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_trigger();

Supported trigger timing: BEFORE, AFTER. Supported events: INSERT, UPDATE, DELETE. Both FOR EACH ROW and FOR EACH STATEMENT are supported.

Sequences

SQL

CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;
SELECT NEXTVAL('order_seq');
SELECT CURRVAL('order_seq');
SELECT LASTVAL();
SELECT SETVAL('order_seq', 2000);
DROP SEQUENCE order_seq;

▶ Run

Sequence options: START, INCREMENT, MINVALUE, MAXVALUE, CACHE, CYCLE.

ALTER SEQUENCE cannot change any of them after creation — it supports only OWNER TO and OWNED BY, so RESTART, INCREMENT BY, RENAME TO and every other clause raise ERROR: ALTER SEQUENCE not supported (0A000). Use SETVAL() to reposition a sequence. Note that DROP SEQUENCE does not check column defaults that depend on the sequence — see DDL — Other DDL for both caveats.

See also: DDL — Identity columns for GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY.

Custom Types

SQL

-- Enum type
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
ALTER TYPE mood ADD VALUE 'excited';

-- Composite type
CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT);

▶ Run

Collations

SQL

CREATE COLLATION my_collation (LOCALE = 'en_US.utf8');
DROP COLLATION my_collation;

▶ Run

A collation you create is real: it persists across sessions, and its locale rules are actually applied — both in an expression and when declared on a column.

SQL

CREATE COLLATION my_collation (LOCALE = 'en_US.utf8');
CREATE TABLE coll_demo (t TEXT COLLATE my_collation);
INSERT INTO coll_demo VALUES ('B'), ('a');

SELECT t FROM coll_demo ORDER BY t;
-- a
-- B

A TEXT column declared without a collation sorts the same way as COLLATE "C" — by byte value — so the same two rows come back in the opposite order (B, then a).

The catalog, however, does not reflect any of this. Two lookups you might reach for are misleading:

Lookup	What it returns	Reality
SELECT collname FROM pg_collation	only C, POSIX, default	your collation exists
pg_attribute.attcollation for a COLLATE column	100, the default OID — identical to a column declared with no collation	the collation is applied at query time

So do not use either one to check whether a collation exists. Create it again instead: if it is already there, that fails with ERROR: collation "my_collation" already exists (42710) — and if it was not, remember to DROP COLLATION the one you just made. For a column there is no catalog route at all; the declared collation is only observable in the sort order.