CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; defines a function that queries can call like a built-in one. The body goes between $$ marks, in plain SQL or in PL/pgSQL, PostgreSQL's procedural language. CREATE PROCEDURE (PostgreSQL 11+) defines a routine you run with CALL instead.
with_tax(50) uses the default rate and returns 60; with_tax(50, 0.1) returns 55. Arguments can also be passed by name: with_tax(50, rate => 0.1).
Write logic in PL/pgSQL
Use LANGUAGE plpgsql when the function needs variables, IF, loops or error handling. Variables go in a DECLARE section, and the code goes between BEGIN and END:
A 4 kg parcel costs the base 5; a 20 kg express parcel costs (5 + 10 * 0.5) * 2 = 20. Inside PL/pgSQL, RAISE NOTICE 'value: %', cost; prints a message and RAISE EXCEPTION aborts with an error.
When plain SQL can express the logic, prefer LANGUAGE sql: the planner can inline a simple SQL function into the calling query.
Return a set of rows
RETURNS TABLE (...) makes a function that returns rows. Call it in FROM like a table:
orders_of('ana') returns orders 1 and 3. RETURNS SETOF orders returns whole rows of the orders table instead of the listed columns.
Since PostgreSQL 14, a SQL function can have a standard SQL body instead of a string. PostgreSQL stores it parsed and records the tables and columns it uses, so they cannot be dropped while the function exists:
CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
SELECT count(*) FROM orders WHERE customer = who;
END;
Create a stored procedure and run it with CALL
A procedure has no return type; you run it with CALL. It can change data, and when the CALL is not inside an explicit transaction block, it can COMMIT partway through. That is how you process a large job in batches.
Account 1 ends with 70 and account 2 with 80. A transfer of 200 would break the CHECK constraint on account 1, and the error rolls back both updates.
| Function | Procedure | |
|---|---|---|
| Run with | SELECT f(...), anywhere in a query | CALL p(...) only |
| Returns | a value, a row or a set of rows | nothing (or OUT/INOUT parameters) |
COMMIT / ROLLBACK inside | no | yes, in PL/pgSQL, when not called inside a transaction block |
| Since | always | PostgreSQL 11 |
Mark the volatility: IMMUTABLE, STABLE, VOLATILE
The volatility label tells the planner what it may assume about a function. The default is VOLATILE.
IMMUTABLE: the result depends only on the arguments and never changes (with_tax). Required for functions used in an index expression.STABLE: the result can change between statements but not within one, for example a function that reads tables (orders_of).VOLATILE: anything else, such as functions that userandom()or change data. PostgreSQL calls it again for every row.
A wrong label gives wrong results: an IMMUTABLE function that reads a table can return stale values from an index or a cached plan.
Replace, overload and drop a function
CREATE OR REPLACE FUNCTION changes the body of an existing function but cannot change its return type; drop it first for that. PostgreSQL identifies a function by its name and argument types, so f(int) and f(text) are two functions. To drop one of two functions with the same name, give the argument types:
CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;
The PL/pgSQL chapter documents loops, cursors, exceptions and dynamic SQL with EXECUTE. To run a function automatically when rows change, attach it to a trigger.
Frequently Asked Questions
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(x)). A procedure, added in PostgreSQL 11, is run on its own with CALL and can COMMIT or ROLLBACK inside its body; a function always runs inside the caller's transaction.
What does $$ mean in a PostgreSQL function?
$$ is dollar quoting: it marks the start and end of a string literal, so the function body can contain single quotes without escaping them. You can also add a tag, as in $body$ ... $body$.
How do I see the code of a PostgreSQL function?
In psql, \df lists functions and \sf name prints a function's definition. In SQL, SELECT pg_get_functiondef('name'::regproc); returns the full CREATE OR REPLACE FUNCTION statement.
Can a PostgreSQL function return multiple rows?
Yes. Declare it RETURNS TABLE (col type, ...) or RETURNS SETOF type and call it in the FROM clause like a table: SELECT * FROM f(1);.