Advanced Function Concepts

1. Handling Exceptions

BEGIN
   INSERT INTO users(email) VALUES(p_email);
EXCEPTION
   WHEN unique_violation THEN
      RAISE NOTICE 'duplicate email %', p_email;
   WHEN OTHERS THEN
      RAISE;
END;

Condition	Catches
unique_violation	23505
foreign_key_violation	23503
not_null_violation	23502
division_by_zero	22012
OTHERS	All non-system errors

2. Using RAISE Statement

RAISE NOTICE 'value = %', x;
RAISE WARNING 'low stock for sku %', sku;
RAISE EXCEPTION 'invalid input: %', val
      USING ERRCODE = '22023', HINT = 'must be positive';

Level	Behavior
DEBUG / LOG / INFO	Server log only at client_min_messages
NOTICE / WARNING	Shown to client
EXCEPTION	Aborts transaction (unless caught)

3. Using ASSERT Statement

ASSERT count_var > 0, 'expected non-empty result';

Note: Controlled by plpgsql.check_asserts (default on). Use for invariants, not user-input validation.

4. Using RETURN Statement

RETURN x + 1;             -- scalar
RETURN NEXT row_var;      -- accumulate set
RETURN QUERY SELECT * FROM customers WHERE active;
RETURN;                   -- end of SETOF function

5. Using Function Overloading

CREATE FUNCTION add(int, int)         RETURNS int    AS $ SELECT $1+$2 $ LANGUAGE sql;
CREATE FUNCTION add(numeric, numeric) RETURNS numeric AS $ SELECT $1+$2 $ LANGUAGE sql;

6. Using DEFAULT Parameter Values

CREATE FUNCTION greet(name text DEFAULT 'world')
RETURNS text LANGUAGE sql AS $ SELECT 'Hello, ' || name $;
SELECT greet();             -- 'Hello, world'

7. Using Named Parameters

SELECT greet(name => 'Alice');
-- positional + named mix allowed left-to-right

8. Using STRICT Keyword

Modifier	Effect
STRICT	Return NULL if any arg is NULL (skip body)
CALLED ON NULL INPUT	Always invoke (default)

9. Using IMMUTABLE, STABLE, VOLATILE

Volatility	Meaning
IMMUTABLE	Same args → same result, no side effects (can index expression)
STABLE	Within statement, deterministic (e.g. current_user)
VOLATILE	Default; may change anytime (e.g. random())

10. Using SECURITY DEFINER vs SECURITY INVOKER

CREATE FUNCTION secret_op() RETURNS void
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = pg_catalog, public AS $ BEGIN ... END $;

Warning: SECURITY DEFINER runs with the owner's privileges. ALWAYS set search_path explicitly to prevent schema-spoofing attacks.

11. Using PARALLEL SAFE, PARALLEL UNSAFE, PARALLEL RESTRICTED

Mode	Meaning
UNSAFE (default)	Cannot run in parallel workers
RESTRICTED	Only on leader process
SAFE	Anywhere — required for parallel scans

12. Listing Functions

\df
\df+ schema.*
SELECT proname, pg_get_function_identity_arguments(oid)
  FROM pg_proc WHERE pronamespace = 'public'::regnamespace;

13. Viewing Function Definition

\sf transfer
SELECT pg_get_functiondef('transfer(int,int,numeric)'::regprocedure);

14. Dropping Functions

DROP FUNCTION IF EXISTS transfer(int, int, numeric);
DROP FUNCTION transfer;            -- only if unique by name