Functions Basics

# Local dev with Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

CREATE OR REPLACE FUNCTION app.account_order_count(p_account_id bigint)
RETURNS bigint
LANGUAGE sql
STABLE
AS $$
  SELECT count(*) FROM app.orders WHERE account_id = p_account_id;
$$;

CREATE OR REPLACE FUNCTION app.is_vip(p_account_id bigint)
RETURNS boolean
LANGUAGE plpgsql
STABLE
AS $$
DECLARE v_total numeric;
BEGIN
  SELECT coalesce(sum(total),0) INTO v_total FROM app.orders WHERE account_id = p_account_id;
  RETURN v_total > 10000;
END;
$$;

SELECT id, app.account_order_count(id) AS orders FROM app.accounts;

CREATE OR REPLACE FUNCTION app.big_orders(p_min numeric)
RETURNS TABLE (order_id bigint, total numeric)
LANGUAGE sql
STABLE
AS $$
  SELECT id, total FROM app.orders WHERE total >= p_min;
$$;

CREATE OR REPLACE FUNCTION app.norm_email(e text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$ SELECT lower(trim(e)); $$;

CREATE OR REPLACE FUNCTION app.norm_email(e text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$ SELECT lower(trim(e)); $$;

CREATE OR REPLACE PROCEDURE app.archive_old_orders()
LANGUAGE plpgsql
AS $$
BEGIN
  DELETE FROM app.orders WHERE created_at < now() - interval '2 years';
END;
$$;
CALL app.archive_old_orders();

ALTER FUNCTION app.account_order_count(bigint) SECURITY INVOKER;

CREATE OR REPLACE FUNCTION app.admin_stat()
RETURNS int LANGUAGE sql SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$ SELECT 1; $$;