Menu
CoddyTech

PostgreSQL Functions, Procedures and Triggers Interview Questions

Functions and procedures, PL/pgSQL, volatility, triggers, set-returning functions, error handling, SECURITY DEFINER and extensions.

11 questions
  1. What is the difference between a function and a procedure in PostgreSQL?

    A function returns a value and is called inside a query (SELECT f()); a procedure is run with CALL and can COMMIT or ROLLBACK inside its body, which a function never can.

    PostgreSQL
    CREATE TABLE batches (id int);
    CREATE PROCEDURE load_batches() LANGUAGE plpgsql AS $$
    BEGIN
      INSERT INTO batches VALUES (1);
      COMMIT;
      INSERT INTO batches VALUES (2);
      ROLLBACK;
    END $$;
    CALL load_batches();
    SELECT * FROM batches;

    Only row 1 survives: the COMMIT kept it, the ROLLBACK undid row 2. Procedures arrived in PostgreSQL 11 and return data through INOUT parameters (OUT too since PostgreSQL 14). Called inside an explicit transaction block, a procedure cannot commit.

  2. What is PL/pgSQL, and when is a plain SQL function enough?

    PL/pgSQL is PostgreSQL's built-in procedural language: variables, IF, loops and exception handling around SQL. A LANGUAGE sql function is a list of SQL statements that returns the last one's result. Prefer it when one query does the job: the planner can inline a simple SQL function into the calling query.

    PostgreSQL
    CREATE FUNCTION tax(amount numeric) RETURNS numeric
    LANGUAGE sql IMMUTABLE AS $$ SELECT round(amount * 0.18, 2) $$;
    
    CREATE FUNCTION grade(score int) RETURNS text
    LANGUAGE plpgsql IMMUTABLE AS $$
    BEGIN
      IF score >= 90 THEN RETURN 'A';
      ELSIF score >= 75 THEN RETURN 'B';
      END IF;
      RETURN 'C';
    END $$;
    
    SELECT tax(1000) AS tax, grade(82) AS grade;

    It returns 180 and B. Reach for PL/pgSQL when you need control flow, several dependent steps or an EXCEPTION block.

  3. What is a DO block in PostgreSQL?

    DO runs an anonymous code block (PL/pgSQL by default) once, without creating a function. Use it for one-off scripts, migrations and loops. It takes no parameters and returns nothing, so it works through side effects.

    PostgreSQL
    CREATE TABLE squares (n int, sq int);
    DO $$
    BEGIN
      FOR i IN 1..5 LOOP
        INSERT INTO squares VALUES (i, i * i);
      END LOOP;
      RAISE NOTICE 'inserted % rows', 5;
    END $$;
    SELECT * FROM squares;

    RAISE NOTICE sends a message to the client (psql prints it) without stopping anything; RAISE EXCEPTION aborts. Run outside a transaction block, a DO block may also COMMIT, which lets a large backfill commit in batches.

  4. How do you write a trigger that keeps an updated_at column current in PostgreSQL?

    Write a trigger function that sets NEW.updated_at and attach it as a BEFORE UPDATE ... FOR EACH ROW trigger: a BEFORE row trigger may change the row before it is written.

    PostgreSQL
    CREATE TABLE products (id int PRIMARY KEY, price int,
      updated_at timestamptz NOT NULL DEFAULT now());
    CREATE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
    BEGIN
      NEW.updated_at := now();
      RETURN NEW;
    END $$;
    CREATE TRIGGER products_updated_at BEFORE UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();
    
    INSERT INTO products VALUES (1, 100, '2020-01-01');
    UPDATE products SET price = 120 WHERE id = 1;
    SELECT id, price, updated_at > '2020-01-01' AS touched FROM products;

    It returns 1 | 120 | true. now() is the transaction's start time, so every row in one transaction gets the same stamp; clock_timestamp() gives wall-clock time. Add WHEN (OLD.* IS DISTINCT FROM NEW.*) to skip updates that change nothing. See trigger functions.

  5. What is the difference between BEFORE and AFTER triggers, and between row-level and statement-level triggers?

    A BEFORE row trigger runs before the row is written. It can change the row (edit and return NEW) or skip it (return NULL). An AFTER trigger runs once the change is done: it sees the final row, and its return value is ignored.

    FOR EACH ROW fires once per affected row; FOR EACH STATEMENT once per statement, even when no row changed. Since PostgreSQL 10 a statement trigger can read all changed rows through a transition table:

    PostgreSQL
    CREATE TABLE payments (id int, amount int);
    CREATE TABLE audit (op text, row_count int);
    CREATE FUNCTION log_batch() RETURNS trigger LANGUAGE plpgsql AS $$
    BEGIN
      INSERT INTO audit SELECT TG_OP, count(*) FROM new_rows;
      RETURN NULL;
    END $$;
    CREATE TRIGGER payments_audit AFTER INSERT ON payments
    REFERENCING NEW TABLE AS new_rows
    FOR EACH STATEMENT EXECUTE FUNCTION log_batch();
    INSERT INTO payments VALUES (1, 50), (2, 70), (3, 20);
    SELECT * FROM audit;

    One audit row, INSERT | 3, for the three-row insert.

  6. In what order do two BEFORE triggers on the same table fire in PostgreSQL?

    PostgreSQL
    CREATE TABLE t (x int);
    CREATE FUNCTION add10() RETURNS trigger LANGUAGE plpgsql AS $$
    BEGIN NEW.x := NEW.x + 10; RETURN NEW; END $$;
    CREATE FUNCTION dbl() RETURNS trigger LANGUAGE plpgsql AS $$
    BEGIN NEW.x := NEW.x * 2; RETURN NEW; END $$;
    CREATE TRIGGER b_add10 BEFORE INSERT ON t FOR EACH ROW EXECUTE FUNCTION add10();
    CREATE TRIGGER a_double BEFORE INSERT ON t FOR EACH ROW EXECUTE FUNCTION dbl();
    INSERT INTO t VALUES (1);
    SELECT x FROM t;

    It returns 12. Triggers of the same kind on the same event fire in alphabetical order of name, not creation order: a_double runs first (1 becomes 2), then b_add10 (2 becomes 12). Each BEFORE row trigger receives the NEW returned by the previous one; if one returns NULL, the row and the remaining triggers are skipped. When order matters, show it in the names (t10_normalize, t20_validate).

  7. How does a PostgreSQL function return a set of rows (RETURNS TABLE vs RETURNS SETOF)?

    RETURNS SETOF type returns rows of an existing type (a table's row type or a scalar); RETURNS TABLE (...) declares the output columns inline. Either way you call it in FROM, like a table.

    PostgreSQL
    CREATE TABLE orders (id int, customer text, amount int);
    INSERT INTO orders VALUES (1, 'asha', 500), (2, 'ravi', 300), (3, 'asha', 700);
    
    CREATE FUNCTION customer_totals(min_total int)
    RETURNS TABLE (customer text, total bigint)
    LANGUAGE sql STABLE AS $$
      SELECT o.customer, sum(o.amount) FROM orders o
      GROUP BY o.customer HAVING sum(o.amount) >= min_total
    $$;
    
    SELECT * FROM customer_totals(400);

    It returns asha | 1200. In PL/pgSQL, fill the result with RETURN QUERY or RETURN NEXT. The trap there: the TABLE column names are also variables inside the body, so an unqualified customer in the query raises "column reference is ambiguous". Qualify columns with a table alias.

  8. What do IMMUTABLE, STABLE and VOLATILE mean on a PostgreSQL function?

    They tell the planner what it may assume. VOLATILE (the default) may return something new on every call or modify data, so it runs for every row. STABLE gives the same result for the same arguments within one statement, like now(). IMMUTABLE gives the same result forever, like lower(), so it can be evaluated at plan time.

    • Only IMMUTABLE functions are allowed in index expressions and generated columns.
    • An index serves col = f() only when f is not VOLATILE.
    • PostgreSQL trusts the label, even when it is wrong.
    PostgreSQL
    -- fails on purpose: timestamptz to date depends on the TimeZone setting
    CREATE TABLE events (id int, at timestamptz);
    CREATE INDEX ON events ((at::date));

    More in function volatility.

  9. Does an EXCEPTION block in PL/pgSQL roll back the work done inside it?

    PostgreSQL
    CREATE TABLE log (msg text);
    CREATE FUNCTION safe_div(a int, b int) RETURNS int LANGUAGE plpgsql AS $$
    BEGIN
      INSERT INTO log VALUES ('before');
      BEGIN
        INSERT INTO log VALUES ('inside');
        RETURN a / b;
      EXCEPTION WHEN division_by_zero THEN
        INSERT INTO log VALUES ('caught');
        RETURN NULL;
      END;
    END $$;
    SELECT safe_div(1, 0);
    SELECT string_agg(msg, ',') FROM log;

    It returns before,caught. A BEGIN ... EXCEPTION block runs as a subtransaction: when it catches an error, everything the block did is rolled back (the inside row), work before the block stays, and the handler runs. An uncaught error propagates and aborts the calling statement.

    Follow-ups: each entry into such a block costs a subtransaction, so keep it out of hot loops; inside the handler, SQLSTATE and SQLERRM give the error. Raise your own with RAISE EXCEPTION 'amount % is negative', amt.

  10. What is SECURITY DEFINER in PostgreSQL, and what makes it risky?

    A SECURITY DEFINER function runs with its owner's privileges instead of the caller's, like setuid on Unix: a low-privilege role gets one controlled action.

    The risks:

    • search_path hijacking: a caller can plant an object with the same name as one the function uses unqualified and have it run as the owner. Pin search_path.
    • EXECUTE goes to PUBLIC by default. Revoke it.
    • Dynamic SQL from arguments is injection with the owner's rights. Use format('%I', ...) and USING.
    CREATE FUNCTION app.log_event(msg text) RETURNS void
    LANGUAGE sql SECURITY DEFINER
    SET search_path = pg_catalog, pg_temp
    AS $$ INSERT INTO app.audit (msg) VALUES (msg) $$;
    
    REVOKE EXECUTE ON FUNCTION app.log_event(text) FROM PUBLIC;
    GRANT EXECUTE ON FUNCTION app.log_event(text) TO app_user;
    

    See CREATE FUNCTION, "Writing SECURITY DEFINER Functions Safely".

  11. What are PostgreSQL extensions, and how do you install one?

    An extension is a package of SQL objects, often with C code (types, functions, operators, index methods), added to one database with CREATE EXTENSION. Its files must already be installed on the server.

    SELECT name, default_version FROM pg_available_extensions ORDER BY name;
    CREATE EXTENSION IF NOT EXISTS pg_trgm;  -- trigram similarity and indexes
    ALTER EXTENSION pg_trgm UPDATE;          -- after a newer version is installed
    \dx                                      -- psql: list installed extensions
    

    Common ones: pg_stat_statements (also needs shared_preload_libraries and a restart), pg_trgm, pgcrypto, postgis. Since PostgreSQL 13, extensions marked trusted can be installed by a non-superuser with CREATE privilege on the database; the rest need a superuser.

Coddy programming languages illustration

Learn to code with Coddy

Get started