Advanced SQL

PL/pgSQL

DB9 supports PL/pgSQL with variable declarations, IF/ELSIF, CASE, FOR loops (with EXIT WHEN and CONTINUE WHEN), PERFORM, RAISE, RETURN, and dynamic EXECUTE — including INTO and USING — in both a CREATE FUNCTION body and a DO block.

WHILE and FOREACH loops and cursor operations are not supported anywhere. BEGIN ... EXCEPTION blocks and nested BEGIN ... END blocks work only inside a DO block. Inside a CREATE FUNCTION body they fail with 0A000 — but at call time, not at create time: CREATE FUNCTION succeeds and the function is registered in pg_proc, then every call raises PL/pgSQL EXCEPTION requires a Session-owned interactive DO host or nested PL/pgSQL blocks require a Session-owned interactive DO host. A migration that only replays DDL will therefore look clean. Migrate stored procedures that rely on these features before switching to DB9.

DB9 supports PL/pgSQL functions and procedures with variable declarations and standard control flow.

Multi-line DECLARE entries parse correctly — a declaration whose type or value spans several lines produces the same value as PostgreSQL, in both a DO block and a CREATE FUNCTION body:

DECLARE v int := 1
  + 2;               -- 3
DECLARE s text := 'abc'
  || 'def';          -- 'abcdef'
DECLARE v int
  := 42;             -- 42

Dynamic SQL and exception handling

EXECUTE works in a CREATE FUNCTION body as well as a DO block. The command string may be a literal, a variable, or an expression, and INTO and USING behave as in PostgreSQL:

CREATE FUNCTION count_rows(t TEXT) RETURNS INT AS $$
DECLARE n INT;
BEGIN
  EXECUTE 'SELECT count(*) FROM ' || quote_ident(t) INTO n;
  RETURN n;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION double_it(p INT) RETURNS INT AS $$
DECLARE n INT;
BEGIN
  EXECUTE 'SELECT $1 * 2' INTO n USING p;
  RETURN n;
END;
$$ LANGUAGE plpgsql;

Exception handling (BEGIN ... EXCEPTION) still requires a DO block:

DO $$
BEGIN
  EXECUTE 'CREATE TABLE audit_snapshot(x int)';
EXCEPTION WHEN others THEN
  RAISE NOTICE 'setup skipped';
END;
$$;

Exception handlers follow PostgreSQL rollback semantics: statements that ran before the exception are rolled back, and only the handler’s effects persist.

CASE statements

CASE works in both function bodies and DO blocks, in simple and searched form:

CREATE FUNCTION size_label(n INT) RETURNS TEXT AS $$
BEGIN
  CASE
    WHEN n > 100 THEN RETURN 'large';
    WHEN n > 10 THEN RETURN 'medium';
    ELSE RETURN 'small';
  END CASE;
END;
$$ LANGUAGE plpgsql;

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;

SELECT … INTO

SELECT ... INTO variable assigns a query result to a declared variable.

CREATE FUNCTION user_count() RETURNS INT AS $$
DECLARE n INT;
BEGIN
  SELECT count(*) INTO n FROM users;
  RETURN n;
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.

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:

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 triggers on INSERT, UPDATE, and DELETE. FOR EACH STATEMENT is accepted but does not get statement-level semantics — see the caution below.

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. A BEFORE trigger function runs as PL/pgSQL: it can assign to NEW (NEW.name := upper(NEW.name);) and the modified row is what gets stored, skip the row with RETURN NULL, or reject it with RAISE EXCEPTION — conditionally, inside IF … END IF, as well as unconditionally:

CREATE FUNCTION reject_bad() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.name = 'bad' THEN
    RAISE EXCEPTION 'rejected';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER guard BEFORE INSERT ON users FOR EACH ROW EXECUTE FUNCTION reject_bad();

INSERT INTO users VALUES (1, 'bad');   -- ERROR: rejected (P0001) — nothing is written

A BEFORE trigger function cannot use loops, CASE statements, EXECUTE, or nested blocks

CREATE TRIGGER ... BEFORE ... checks the trigger function’s body, and refuses one that uses a construct DB9 cannot run in a BEFORE trigger — the trigger is not created:

Construct in the function body	`CREATE TRIGGER ... BEFORE` fails with
`FOR i IN 1..n LOOP`	`0A000` `BEFORE trigger functions do not support PL/pgSQL FOR range statements`
`FOR r IN SELECT … LOOP`	`0A000` `… do not support PL/pgSQL FOR query statements`
bare `LOOP … END LOOP`	`0A000` `… do not support PL/pgSQL LOOP statements`
`WHILE` or `FOREACH` loop	`42601` syntax error, as in any function
`CASE` statement	`0A000` `… do not support PL/pgSQL CASE statements`
`EXECUTE`	`0A000` `… do not support PL/pgSQL EXECUTE statements`
`EXCEPTION` handler	`0A000` `BEFORE trigger functions do not support EXCEPTION handlers`
nested `BEGIN … END`	`0A000` `… do not support PL/pgSQL block statements`

The same check runs again when the trigger fires, so replacing an attached function with CREATE OR REPLACE FUNCTION does not get around it: every statement that fires the trigger then fails with the same 0A000 and writes nothing.

What a validation or rewrite trigger usually needs all works: IF / ELSIF / ELSE, RAISE EXCEPTION, PERFORM, DECLAREd locals, SELECT … INTO, assignments to NEW from any expression (a local variable, or a subquery against another table), DML against other tables, and TG_OP / TG_TABLE_NAME. A CASE expression on the right of an assignment (NEW.col := CASE WHEN … END) is fine; rewrite a CASE statement as IF / ELSIF, or move logic that needs a FOR loop, a CASE statement or EXECUTE into an AFTER trigger, which runs them. This applies to BEFORE INSERT, BEFORE UPDATE and BEFORE DELETE alike, on both the wire protocol and the HTTP SQL API.

In an AFTER trigger, RETURN NEW; and RETURN OLD; must be alone on their line

In an AFTER trigger, DB9 only recognises NEW / OLD in a RETURN when that RETURN is the whole line. Share the line with anything else and NEW falls through to normal name resolution, so the trigger fails at run time — on both the wire protocol and the HTTP SQL API — with:

ERROR:  column "NEW" does not exist   -- SQLSTATE 42703

The statement that fired the trigger is rolled back with it, and the error names a column you never wrote, so it points nowhere near the real cause.

-- Fails: RETURN NEW shares its line
CREATE FUNCTION t_bad() 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;

-- Works: RETURN NEW is the only statement on its line
CREATE FUNCTION t_ok() 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;

Only the bare RETURN NEW / RETURN OLD form is affected. Qualified references such as NEW.name are resolved normally wherever they appear, and RETURN NULL; — the usual AFTER trigger ending — works on a shared line because NULL is a literal, not a pseudo-record. Formatters and ORMs that emit a compact one-line body are the common way to hit this.

A BEFORE trigger is not affected: it runs the same one-line body correctly, INSERT included. So a function that works as a BEFORE trigger can still fail when it is attached AFTER.

FOR EACH STATEMENT runs once per row

CREATE TRIGGER ... FOR EACH STATEMENT is accepted, but DB9 does not model the statement level — the trigger executes with FOR EACH ROW semantics. Nothing warns you, and the catalog does not give it away either: information_schema.triggers reports the declared action_orientation as STATEMENT, which is what you asked for, not how it runs.

Measured against a table with one AFTER ... FOR EACH STATEMENT trigger per event:

Statement	PostgreSQL	DB9
`INSERT` of 3 rows	1 execution	3
`UPDATE` matching 3 rows	1 execution	3
`UPDATE` matching 0 rows	1 execution	0
`DELETE` matching 0 rows	1 execution	0

The zero-row rows are the ones that bite: a statement-level trigger written to record “this table was written to” never fires when the statement matches nothing, and over-counts by a factor of the row count when it does. Write the logic as an explicit FOR EACH ROW trigger so the per-row behaviour is intentional, or do the once-per-statement work in the application.

DROP TRIGGER on a trigger that does not exist fails with a bare internal error (XX000), where PostgreSQL raises 42704. Use DROP TRIGGER IF EXISTS in migration scripts.

Sequences

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;

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 a bare ERROR: internal error carrying 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

-- 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);

PostgreSQL orders an enum by the position of each label in CREATE TYPE. DB9 compares enum values as their label text, so ordering follows the alphabet instead — without any error:

CREATE TYPE priority AS ENUM ('low', 'medium', 'high', 'critical');
CREATE TABLE tickets (id INT PRIMARY KEY, p priority);
INSERT INTO tickets VALUES (1, 'high'), (2, 'low'), (3, 'critical'), (4, 'medium');

SELECT p FROM tickets ORDER BY p;                -- critical, high, low, medium  (PostgreSQL: low, medium, high, critical)
SELECT max(p) FROM tickets;                      -- medium                       (PostgreSQL: critical)
SELECT count(*) FROM tickets WHERE p >= 'high';  -- 3                            (PostgreSQL: 2)

The same applies to <, >, BETWEEN, min, greatest, and least, and it does not change when the column has an index. pg_enum.enumsortorder does record the declared order — including values placed with ADD VALUE … BEFORE or AFTER — but comparisons do not consult it. enum_first(), enum_last() and enum_range() do not exist (42883).

Where order matters, sort on an explicit rank rather than on the enum itself:

SELECT p FROM tickets
ORDER BY array_position(ARRAY['low', 'medium', 'high', 'critical'], p::text);
-- low, medium, high, critical

Equality, IN, and grouping are unaffected. See DDL — Other DDL for which ALTER TYPE and DROP TYPE forms are supported.

Collations

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

A collation you create is real: it persists across sessions, and declaring it on a column changes how that column sorts and compares.

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 same applies to comparisons: ('a' COLLATE my_collation) < 'B' is true, while an uncollated 'a' < 'B' is false.

The LOCALE you name is ignored

Declaring any collation switches the column to one fixed locale-aware order. The LOCALE value has no effect — a collation created with LOCALE = 'C' sorts identically to one created with LOCALE = 'en_US.utf8':

CREATE COLLATION coll_c  (LOCALE = 'C');
CREATE COLLATION coll_en (LOCALE = 'en_US.utf8');
-- rows 'a', 'A', 'b', 'B'
-- COLLATE coll_c   -> a, A, b, B      PostgreSQL's C collation gives A, B, a, b
-- COLLATE coll_en  -> a, A, b, B
-- no collation     -> A, B, a, b

So you can choose between byte order and locale order, but not between locales. DB9’s database-default ordering is bytewise where PostgreSQL follows the database locale — a known parity gap (DB9-FEAT-132).

Aggregate and window ORDER BY drop the collation

A collation applies to a plain ORDER BY on the column, but not to an ORDER BY written inside an aggregate, nor to a window’s ORDER BY. Those silently fall back to byte order on the same column, in the same query:

-- rows 'a', 'A', 'b', 'B' in a column declared COLLATE my_collation
SELECT t FROM cx ORDER BY t;                      -- a, A, b, B   (collated)
SELECT string_agg(t, ',' ORDER BY t) FROM cx;     -- A,B,a,b      (byte order)
SELECT array_agg(t ORDER BY t) FROM cx;           -- {A,B,a,b}    (byte order)
SELECT t, row_number() OVER (ORDER BY t) FROM cx; -- A, B, a, b   (byte order)

If you need collated ordering inside an aggregate, sort in a subquery first and aggregate over the result.

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 column does carry the declared collation

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.