PostgreSQL Functions and PL/pgSQL

PostgreSQL lets you write reusable server-side logic in PL/pgSQL — a procedural language that adds variables, conditionals, and loops on top of plain SQL.

A Simple Function

CREATE OR REPLACE FUNCTION get_employee_count(dept TEXT)
RETURNS INTEGER AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM employees WHERE department = dept);
END;
$$ LANGUAGE plpgsql;

SELECT get_employee_count('Engineering');
-- 4

$$ ... $$ is a "dollar-quoted" string — it lets you write multi-line code without escaping every quote inside it.

Using Variables

CREATE OR REPLACE FUNCTION give_raise(emp_id INTEGER, percent NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
  new_salary NUMERIC;
BEGIN
  UPDATE employees
  SET salary = salary * (1 + percent / 100)
  WHERE id = emp_id
  RETURNING salary INTO new_salary;

  RETURN new_salary;
END;
$$ LANGUAGE plpgsql;

SELECT give_raise(1, 10);
-- returns the employee's NEW salary after a 10% raise

Conditionals and Loops

CREATE OR REPLACE FUNCTION salary_band(salary NUMERIC)
RETURNS TEXT AS $$
BEGIN
  IF salary < 40000 THEN
    RETURN 'Junior';
  ELSIF salary < 80000 THEN
    RETURN 'Mid';
  ELSE
    RETURN 'Senior';
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION print_departments()
RETURNS VOID AS $$
DECLARE
  dept RECORD;
BEGIN
  FOR dept IN SELECT DISTINCT department FROM employees LOOP
    RAISE NOTICE 'Department: %', dept.department;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

FUNCTION vs PROCEDURE

• A FUNCTION always returns a value and can be used inside a SELECT.
• A PROCEDURE doesn't return a value, can manage its own transactions (COMMIT/ROLLBACK inside it), and is called with CALL instead of SELECT.

CREATE PROCEDURE archive_old_employees()
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO archived_employees SELECT * FROM employees WHERE is_active = false;
  DELETE FROM employees WHERE is_active = false;
END;
$$;

CALL archive_old_employees();

Common Errors & Troubleshooting

• ERROR: control reached end of function without RETURN — every code path in a FUNCTION (unlike a PROCEDURE) must eventually hit a RETURN statement; check any IF/ELSIF chain has a matching ELSE or a fallback RETURN after it.
• Changes not visible after calling a function — remember a FUNCTION runs inside the caller's transaction; if the calling transaction later rolls back, the function's changes roll back too — this is expected, not a bug.
• ERROR: syntax error at or near "$$" — usually means an earlier quote or bracket wasn't closed properly inside the function body; the dollar-quoted string itself is rarely the actual problem.

Key Takeaways

• PL/pgSQL adds real programming constructs (DECLARE, IF, LOOP) to plain SQL, executed entirely on the database server.
• Functions return a value and can be called inside a SELECT; procedures don't return a value and are invoked with CALL.
• Dollar-quoting ($$ ... $$) is how you write multi-line function bodies without constantly escaping quotes.