Functions and PL/pgSQL

SQL Functions

CREATE FUNCTION full_name(first text, last text)
RETURNS TEXT AS $$
  SELECT first || ' ' || last;
$$ LANGUAGE SQL;

SELECT full_name('Priya', 'Sharma');

PL/pgSQL

PL/pgSQL is PostgreSQL's procedural language: variables, loops, conditions and exceptions all inside the database.

CREATE FUNCTION discount(total NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
  IF total > 1000 THEN
    RETURN total * 0.9;
  ELSE
    RETURN total;
  END IF;
END;
$$ LANGUAGE plpgsql;

Calculating with Loops

CREATE FUNCTION sum_upto(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
  i    INTEGER;
  total INTEGER := 0;
BEGIN
  FOR i IN 1..n LOOP
    total := total + i;
  END LOOP;
  RETURN total;
END;
$$ LANGUAGE plpgsql;

Key Points

• SQL functions are simple and immutable-friendly.
• PL/pgSQL adds variables, control flow and exceptions.
• Functions live in the database, callable from any client.
• Use functions to centralise logic shared by many apps.