Functions and PL/pgSQL

CREATE FUNCTION add_int(a int, b int)
RETURNS int
LANGUAGE sql IMMUTABLE PARALLEL SAFE
AS $$ SELECT a + b $$;
SELECT add_int(2, 3);
-- 5

DO $$
DECLARE n int := 1;
BEGIN
  n := n + 1;
  RAISE NOTICE 'n=%', n;
END $$;
-- NOTICE: n=2

CREATE FUNCTION recent_ids(lim int)
RETURNS TABLE (id bigint)
LANGUAGE sql AS $$
  SELECT id FROM events ORDER BY ts DESC LIMIT lim
$$;
SELECT * FROM recent_ids(5);
-- up to 5 ids

DO $$
BEGIN
  PERFORM 1 / 0;
EXCEPTION WHEN division_by_zero THEN
  RAISE NOTICE 'caught';
END $$;
-- NOTICE: caught

CREATE FUNCTION touch_updated()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END $$;
-- CREATE TRIGGER ... BEFORE UPDATE ON t FOR EACH ROW EXECUTE FUNCTION touch_updated();

CREATE FUNCTION split_name(full text, OUT first text, OUT last text)
LANGUAGE sql AS $$
  SELECT split_part(full, ' ', 1), split_part(full, ' ', 2)
$$;

-- IMMUTABLE: pure; STABLE: fixed within scan; VOLATILE: default

CREATE FUNCTION admin_only()
RETURNS void
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$ SELECT 1 $$;

CREATE FUNCTION sum_n(VARIADIC xs int[])
RETURNS int LANGUAGE sql AS $$ SELECT coalesce(sum(x),0) FROM unnest(xs) x $$;
SELECT sum_n(1,2,3);
-- 6

CREATE FUNCTION uids()
RETURNS SETOF bigint LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT id FROM users;
END $$;

DO $$ BEGIN
  RAISE EXCEPTION 'bad input' USING ERRCODE = '22023';
END $$;
-- ERROR: bad input

-- CREATE TEMP TABLE t ON COMMIT DROP AS SELECT ...

DROP FUNCTION IF EXISTS add_int(int, int);

-- PARALLEL SAFE on add_int enables parallel plans

CALL reindex_helper();
-- procedures may COMMIT inside (unlike functions)