Advanced SQL

Advanced SQL

PL/pgSQL

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

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 / b;
EXCEPTION
  WHEN division_by_zero THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

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;

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

Collations

SQL

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