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.

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