Functions and PL/pgSQL

Introduction

Functions and procedures store logic inside the database—batch jobs, consistent reporting, or DBA tooling. PostgreSQL uses PL/pgSQL (no DELIMITER hack like MySQL). This chapter covers CREATE FUNCTION, RETURNS TABLE, and CREATE PROCEDURE (transaction control in procedures). Application code often lives in FastAPI or Spring Boot; still learn syntax to read migrations and ops scripts. Compare with MySQL stored procedures.

Prerequisites

• Transactions Basics
• Subqueries and Aggregates
• psql connected to your practice database

Simple SQL Function

Count posts for a user—pure SQL language:

CREATE OR REPLACE FUNCTION fn_post_count(p_user_id BIGINT)
RETURNS BIGINT
LANGUAGE sql
STABLE
AS $$
  SELECT COUNT(*)::bigint FROM posts WHERE user_id = p_user_id;
$$;

Use in queries:

SELECT display_name, fn_post_count(id) AS posts
FROM users;

Drop:

DROP FUNCTION IF EXISTS fn_post_count(BIGINT);

Code explanation:

• STABLE means same args + same DB state → same result within a transaction (good for reads)
• IMMUTABLE for pure math; VOLATILE default for functions that write or have side effects

PL/pgSQL Function Basics

Transfer with balance check—logic in the database:

Call:

SELECT fn_transfer('Alice', 'Bob', 25.00);

Code explanation:

• DECLARE introduces variables; SELECT ... INTO assigns one row
• Function runs in the caller's transaction—COMMIT happens outside unless you use a procedure

RETURNS TABLE

Return multiple columns as a result set:

SELECT * FROM fn_posts_by_user(1);

Alternative with RETURNS SETOF:

CREATE OR REPLACE FUNCTION fn_active_users()
RETURNS SETOF users
LANGUAGE sql
STABLE
AS $$
  SELECT * FROM users WHERE is_active = TRUE;
$$;

OUT Parameters

CREATE OR REPLACE FUNCTION fn_post_count_out(
  p_user_id BIGINT,
  OUT post_count BIGINT
)
LANGUAGE sql
AS $$
  SELECT COUNT(*)::bigint FROM posts WHERE user_id = p_user_id;
$$;

SELECT fn_post_count_out(1);

CREATE PROCEDURE (Postgres 11+)

Procedures can COMMIT / ROLLBACK inside (unlike functions):

Call:

CALL sp_transfer('Alice', 'Bob', 10.00, NULL);
-- Or with a variable for INOUT in plpgsql DO block / app driver

Code explanation:

• CALL invokes procedures; SELECT invokes functions
• Nested COMMIT in procedures is a PostgreSQL specialty—use carefully; many teams keep transactions in app code

Flow Control in PL/pgSQL

Loops and RAISE NOTICE for debugging:

DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT id, title FROM posts LIMIT 3 LOOP
    RAISE NOTICE 'Post %: %', r.id, r.title;
  END LOOP;
END;
$$;

DO runs anonymous PL/pgSQL—handy for one-off scripts.

Exception Handling

CREATE OR REPLACE FUNCTION fn_safe_div(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN a / b;
EXCEPTION
  WHEN division_by_zero THEN
    RETURN NULL;
END;
$$;

Security: SECURITY DEFINER

Functions run as owner by default (SECURITY INVOKER). SECURITY DEFINER runs as function owner—use with strict search_path to avoid privilege escalation:

CREATE OR REPLACE FUNCTION fn_admin_only()
RETURNS TEXT
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$ SELECT 'restricted'; $$;

Grant EXECUTE narrowly—Roles and Permissions.

Functions vs Application Code

Hello Code favors psycopg / JDBC in app layers—psycopg basics—but DB functions appear in real schemas.

FAQ

Function vs procedure?

Functions return values and run in caller transactions. Procedures use CALL, may COMMIT/ROLLBACK, and return status via INOUT params.

Why no DELIMITER in PostgreSQL?

Bodies use $$ ... $$ (or $tag$ ... $tag$) so semicolons inside do not terminate the CREATE statement.

Can functions return multiple rows?

Yes—RETURNS TABLE, RETURNS SETOF, or RETURN QUERY in PL/pgSQL.

STABLE vs IMMUTABLE vs VOLATILE?

Planner uses these for optimization. Mark read-only lookups STABLE; never lie—wrong labels cause stale or incorrect plans.

Replace vs CREATE OR REPLACE?

CREATE OR REPLACE updates body; argument types cannot change—DROP first if signature changes.

Triggers?

Trigger functions are PL/pgSQL too—Triggers next chapter.