How to use PostgreSQL functions

· Category: SQL & Databases

Short answer

PostgreSQL functions encapsulate reusable logic using SQL, PL/pgSQL, or other supported languages. They can return scalars, sets, or composite types.

Steps

  1. Create a SQL function:
CREATE FUNCTION add_one(val INTEGER) RETURNS INTEGER AS $$
  SELECT val + 1;
$$ LANGUAGE SQL;
  1. Create a PL/pgSQL function with variables and control flow.
  2. Return sets with RETURNS TABLE or RETURNS SETOF.
  3. Use IMMUTABLE, STABLE, and VOLATILE labels to help the optimizer.
  4. Drop with DROP FUNCTION add_one(INTEGER);

Tips

  • Use functions to centralize calculation logic and reduce client-side duplication.
  • Overloading functions by argument types is supported and useful for polymorphic APIs.

Common issues

  • Functions executed inside triggers must be declared carefully to avoid mutating-table errors.
  • Heavy use of functions in WHERE clauses can prevent index usage.r