Functions, procedures and PL/pgSQL triggers

Writing SQL and PL/pgSQL functions, returning sets, DO blocks for one-off work, trigger functions, and RAISE with proper exception handling.

Functions

create or replace function invoice_total(p_invoice_id bigint)
returns numeric
language sql
stable
as $$
  select coalesce(sum(quantity * unit_price), 0)
  from invoice_line
  where invoice_id = p_invoice_id;
$$;

create or replace function top_customers(p_limit int default 10)
returns table (customer_id bigint, name text, revenue numeric)
language plpgsql
stable
as $$
begin
  if p_limit is null or p_limit <= 0 then
    raise exception 'limit must be positive' using errcode = '22023';
  end if;

  return query
    select c.id, c.name, sum(i.total_cents)::numeric / 100
    from customer c join invoice i on i.customer_id = c.id
    group by c.id, c.name
    order by 3 desc
    limit p_limit;
end;
$$;

select * from top_customers(5);
select invoice_total(42);

Triggers

create or replace function set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at := now();
  return new;
end;
$$;

create trigger book_set_updated_at
  before update on book
  for each row
  execute function set_updated_at();

-- audit trail with a statement-level trigger on truncate
create or replace function audit_book()
returns trigger
language plpgsql
as $$
begin
  if tg_op = 'DELETE' then
    insert into book_audit (book_id, action, changed_by)
    values (old.id, 'DELETE', current_user);
    return old;
  end if;

  insert into book_audit (book_id, action, changed_by)
  values (new.id, tg_op, current_user);
  return new;
end;
$$;

create trigger book_audit_trg
  after insert or update or delete on book
  for each row execute function audit_book();

• A BEFORE trigger can modify or discard the row by returning new or null; an AFTER trigger's return value is ignored.
• A row-level trigger fires once per row, so a million-row update fires it a million times. Test bulk operations explicitly.
• Triggers are invisible in application code. Document them, and prefer them for invariants that must hold no matter which client writes, such as an audit trail.
• statement_timestamp() is the time the statement started; clock_timestamp() is the current time. Inside a transaction, now() is the transaction start time.

DO blocks and error handling

do $$
declare
  v_count integer;
begin
  select count(*) into v_count from book where search is null and title is not null;
  raise notice 'rows needing a rebuild: %', v_count;

  update book set search = to_tsvector('english', title)
  where search is null and title is not null;
end;
$$;

-- catching an error without aborting the whole transaction
do $$
begin
  insert into book (isbn, title, author_id) values ('dup', 'x', 1);
exception
  when unique_violation then
    raise notice 'already present';
end;
$$;

create or replace function safe_divide(a numeric, b numeric)
returns numeric
language plpgsql
immutable
as $$
begin
  return a / b;
exception
  when division_by_zero then
    return null;
  when others then
    raise exception 'safe_divide failed: %', sqlerrm using errcode = sqlstate;
end;
$$;

• A DO block runs in its own transaction and cannot be rolled back separately from the surrounding one unless you catch the exception inside it.
• An exception block creates an implicit subtransaction, which is not free - do not wrap a hot loop in one.
• Always re-raise with context (using errcode) rather than swallowing: a silently handled error in a trigger is very hard to diagnose later.
• PL/pgSQL is best kept for invariants and small utilities. Complex business logic belongs in the application where it can be tested and versioned.

FAQ

Related

Roles, permissions, schemas and row-level security Data types, tables and constraints

Last refreshed 2026-09-18.