The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Create a SQL functionCREATE FUNCTION app.net_total(numeric, numeric) RETURNS numeric LANGUAGE sql IMMUTABLE RETURN $1 - $2;View examples
Call with named notationSELECT app.calculate_total(subtotal => 100, discount => 5);View examples
Return rowsRETURNS TABLE (order_id bigint, total numeric)View examples
Create PL/pgSQL functionCREATE FUNCTION app.require_positive(value integer) RETURNS integer LANGUAGE plpgsql AS $$ ... $$;View examples
Raise an errorRAISE EXCEPTION 'quantity must be positive' USING ERRCODE = '22023';View examples
Handle an expected errorEXCEPTION WHEN unique_violation THEN ...View examples
Create a procedureCREATE PROCEDURE app.refresh_reports() LANGUAGE plpgsql AS $$ ... $$;View examples
Call a procedureCALL app.refresh_reports();View examples
Declare stable behaviorLANGUAGE sql STABLEView examples
Return null on null inputRETURNS NULL ON NULL INPUTView examples
Run as function ownerSECURITY DEFINER SET search_path = pg_catalog, appView examples
Create trigger functionCREATE FUNCTION app.set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$ ... $$;View examples
Attach row triggerCREATE TRIGGER set_updated_at BEFORE UPDATE ON app.items FOR EACH ROW EXECUTE FUNCTION app.set_updated_at();View examples
Drop an exact signatureDROP FUNCTION app.net_total(numeric, numeric) RESTRICT;View examples

Server-side routines centralize data-adjacent behavior but also introduce privileges, transaction, deployment, and observability concerns. Prefer plain SQL constraints and expressions where sufficient, declare volatility and security truthfully, schema-qualify privileged code, and test routines under the exact caller roles and concurrency they will face.

Step by step

Detailed examples

01

Use SQL-language functions for expression-shaped logic

SQL functions can return a scalar, one row, or a set and are often easier for the planner to understand than procedural code. Parameter types form the function identity, so overloaded calls can become ambiguous. Explicit schemas and casts make resolution predictable.

Immutable arithmetic function
CREATE FUNCTION app.net_total(subtotal numeric, discount numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT
RETURN subtotal - discount;

SELECT app.net_total(100, 5);
Output
net_total
---------
95
Back to quick reference ↑
02

Use procedural blocks for stateful control, not routine queries

PL/pgSQL adds variables, conditions, loops, diagnostics, and exception blocks. SELECT INTO expects a row assignment; STRICT changes zero or multiple rows into errors. An EXCEPTION block creates extra transaction machinery and rolls database changes in that block back before the handler runs, so keep it narrow.

Validate and return a value
CREATE FUNCTION app.require_positive(value integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF value <= 0 THEN
    RAISE EXCEPTION 'quantity must be positive' USING ERRCODE = '22023';
  END IF;
  RETURN value;
END;
$$;
Back to quick reference ↑
03

Choose procedures only when CALL semantics are needed

Procedures are invoked with CALL and do not appear in expressions. They can perform transaction control only in allowed top-level CALL contexts and not when surrounding attributes or call chains prohibit it. A procedure is not automatically more performant or secure than a function.

Operational refresh procedure
CREATE PROCEDURE app.refresh_reporting_data()
LANGUAGE plpgsql
AS $$
BEGIN
  REFRESH MATERIALIZED VIEW app.daily_sales;
  INSERT INTO app.refresh_log(refreshed_at) VALUES (clock_timestamp());
END;
$$;

CALL app.refresh_reporting_data();
Back to quick reference ↑
04

Tell the planner the truth about volatility and parallel safety

VOLATILE is the default and permits changes on every call. STABLE promises consistent results within a statement; IMMUTABLE promises the same result forever for the same arguments. A false IMMUTABLE declaration can fold stale values into plans or indexes. COST, ROWS, and PARALLEL labels also affect planning and must reflect behavior.

Stable lookup function
CREATE FUNCTION app.current_tax_rate(region_code text)
RETURNS numeric
LANGUAGE sql
STABLE
STRICT
AS $$
  SELECT rate FROM app.tax_rates WHERE region = region_code
$$;
Back to quick reference ↑
05

Harden every security-definer routine

SECURITY DEFINER runs with owner privileges and is a privilege boundary. Remove PUBLIC execute unless intended, set a trusted search_path that places temporary or user-writable schemas last or excludes them, schema-qualify objects, and avoid dynamic SQL from untrusted strings. The owner must not be an unnecessarily powerful superuser.

Restricted definer function
CREATE FUNCTION app.account_count()
RETURNS bigint
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = pg_catalog, app
AS $$ SELECT count(*) FROM app.accounts $$;
REVOKE ALL ON FUNCTION app.account_count() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION app.account_count() TO reporting_role;
Back to quick reference ↑
06

Keep trigger timing, level, and return behavior explicit

BEFORE row triggers may modify and return NEW or return null to skip the row; AFTER row trigger return values are ignored. Statement triggers run once and transition tables can expose changed sets. Multiple triggers introduce hidden ordering and recursion risk, so prefer constraints and explicit writes when they express the rule.

Maintain an update timestamp
CREATE FUNCTION app.set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := clock_timestamp();
  RETURN NEW;
END;
$$;
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON app.items
FOR EACH ROW
EXECUTE FUNCTION app.set_updated_at();
Back to quick reference ↑
07

Version signatures, privileges, and dependencies together

CREATE OR REPLACE cannot change a function's name, argument types, or return type arbitrarily. Adding overloads can change resolution. Capture owner, configuration, comments, grants, and dependent objects; test nulls, errors, permissions, plans, concurrent calls, and rollback behavior before deployment.

Inspect routine identity and properties
SELECT n.nspname, p.proname, pg_get_function_identity_arguments(p.oid) AS arguments,
       p.provolatile, p.prosecdef, pg_get_userbyid(p.proowner) AS owner
FROM pg_proc AS p
JOIN pg_namespace AS n ON n.oid = p.pronamespace
WHERE n.nspname = 'app'
ORDER BY p.proname, arguments;
Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupPostgreSQL: CREATE FUNCTIONpostgresql.org
  2. PostgreSQL Global Development GroupPostgreSQL: CREATE PROCEDUREpostgresql.org
  3. PostgreSQL Global Development GroupPostgreSQL: PL/pgSQLpostgresql.org
  4. PostgreSQL Global Development GroupPostgreSQL: Trigger Functionspostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback