Advanced PostgreSQL: Functions, JSON, and SecurityLesson 6.1

How to write PostgreSQL functions and stored procedures

CREATE FUNCTION, PL/pgSQL, RETURNS, DECLARE variables, IF ELSE, FOR loop, EXCEPTION block, CREATE PROCEDURE, CALL, function vs procedure difference

Functions and Procedures

Functions run inside the database server. They reduce round trips, enforce logic centrally, and can be called from SQL.

Simple function

CREATE OR REPLACE FUNCTION get_full_name(
  p_first TEXT,
  p_last  TEXT
) RETURNS TEXT
LANGUAGE plpgsql AS $$
BEGIN
  RETURN p_first || ' ' || p_last;
END;
$$;

SELECT get_full_name('Alice', 'Smith');

Function with logic and exception handling

CREATE OR REPLACE FUNCTION transfer_funds(
  p_from   INT,
  p_to     INT,
  p_amount NUMERIC
) RETURNS VOID
LANGUAGE plpgsql AS $$
DECLARE
  v_balance NUMERIC;
BEGIN
  SELECT balance INTO v_balance FROM accounts WHERE id = p_from FOR UPDATE;
  IF v_balance < p_amount THEN
    RAISE EXCEPTION 'Insufficient funds: % available', v_balance;
  END IF;
  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;
$$;

Procedure vs Function

Procedures (PostgreSQL 11+) can commit or roll back transactions. Functions cannot. Call procedures with CALL, functions with SELECT. Use functions for returning values, procedures for transactional workflows.