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 withCALLand canCOMMITorROLLBACKinside its body, which a function never can.PostgreSQLCREATE 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
COMMITkept it, theROLLBACKundid row 2. Procedures arrived in PostgreSQL 11 and return data throughINOUTparameters (OUTtoo since PostgreSQL 14). Called inside an explicit transaction block, a procedure cannot commit.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. ALANGUAGE sqlfunction 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.PostgreSQLCREATE 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 anEXCEPTIONblock.What is a
DOblock in PostgreSQL?DOruns 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.PostgreSQLCREATE 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 NOTICEsends a message to the client (psql prints it) without stopping anything;RAISE EXCEPTIONaborts. Run outside a transaction block, aDOblock may alsoCOMMIT, which lets a large backfill commit in batches.How do you write a trigger that keeps an
updated_atcolumn current in PostgreSQL?Write a trigger function that sets
NEW.updated_atand attach it as aBEFORE UPDATE ... FOR EACH ROWtrigger: aBEFORErow trigger may change the row before it is written.PostgreSQLCREATE 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. AddWHEN (OLD.* IS DISTINCT FROM NEW.*)to skip updates that change nothing. See trigger functions.What is the difference between
BEFOREandAFTERtriggers, and between row-level and statement-level triggers?A
BEFORErow trigger runs before the row is written. It can change the row (edit and returnNEW) or skip it (returnNULL). AnAFTERtrigger runs once the change is done: it sees the final row, and its return value is ignored.FOR EACH ROWfires once per affected row;FOR EACH STATEMENTonce per statement, even when no row changed. Since PostgreSQL 10 a statement trigger can read all changed rows through a transition table:PostgreSQLCREATE 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.In what order do two
BEFOREtriggers on the same table fire in PostgreSQL?PostgreSQLCREATE 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_doubleruns first (1 becomes 2), thenb_add10(2 becomes 12). EachBEFORErow trigger receives theNEWreturned by the previous one; if one returnsNULL, the row and the remaining triggers are skipped. When order matters, show it in the names (t10_normalize,t20_validate).How does a PostgreSQL function return a set of rows (
RETURNS TABLEvsRETURNS SETOF)?RETURNS SETOF typereturns 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 inFROM, like a table.PostgreSQLCREATE 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 withRETURN QUERYorRETURN NEXT. The trap there: theTABLEcolumn names are also variables inside the body, so an unqualifiedcustomerin the query raises "column reference is ambiguous". Qualify columns with a table alias.What do
IMMUTABLE,STABLEandVOLATILEmean 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.STABLEgives the same result for the same arguments within one statement, likenow().IMMUTABLEgives the same result forever, likelower(), so it can be evaluated at plan time.- Only
IMMUTABLEfunctions are allowed in index expressions and generated columns. - An index serves
col = f()only whenfis notVOLATILE. - 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.
- Only
Does an
EXCEPTIONblock in PL/pgSQL roll back the work done inside it?PostgreSQLCREATE 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. ABEGIN ... EXCEPTIONblock runs as a subtransaction: when it catches an error, everything the block did is rolled back (theinsiderow), 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,
SQLSTATEandSQLERRMgive the error. Raise your own withRAISE EXCEPTION 'amount % is negative', amt.What is
SECURITY DEFINERin PostgreSQL, and what makes it risky?A
SECURITY DEFINERfunction 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_pathhijacking: a caller can plant an object with the same name as one the function uses unqualified and have it run as the owner. Pinsearch_path.EXECUTEgoes toPUBLICby default. Revoke it.- Dynamic SQL from arguments is injection with the owner's rights. Use
format('%I', ...)andUSING.
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".
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 extensionsCommon ones:
pg_stat_statements(also needsshared_preload_librariesand a restart),pg_trgm,pgcrypto,postgis. Since PostgreSQL 13, extensions marked trusted can be installed by a non-superuser withCREATEprivilege on the database; the rest need a superuser.
PostgreSQL Functions, Procedures and Triggers Interview Questions
Functions and procedures, PL/pgSQL, volatility, triggers, set-returning functions, error handling, SECURITY DEFINER and extensions.