Menu

PostgreSQL CREATE FUNCTION and Stored Procedures

CREATE FUNCTION defines a function you call inside queries, written in SQL or PL/pgSQL. CREATE PROCEDURE defines a routine you run with CALL, which can commit transactions as it goes.

This page includes runnable editors. Edit, run, and see output instantly.

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.

FunctionProcedure
Run withSELECT f(...), anywhere in a queryCALL p(...) only
Returnsa value, a row or a set of rowsnothing (or OUT/INOUT parameters)
COMMIT / ROLLBACK insidenoyes, in PL/pgSQL, when not called inside a transaction block
SincealwaysPostgreSQL 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 use random() 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);.

Coddy programming languages illustration

Learn to code with Coddy

Get started