Advanced Function Concepts

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

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

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

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

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;

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

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

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

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

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

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