16.4 절차적 언어

CREATE OR REPLACE FUNCTION calc_discount(price numeric, status text)
RETURNS numeric AS $$
DECLARE
  discount numeric;
BEGIN
  IF status = 'gold' THEN
    discount := price * 0.2;
  ELSIF status = 'silver' THEN
    discount := price * 0.1;
  ELSE
    discount := 0;
  END IF;
  RETURN discount;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 사용
SELECT calc_discount(100000, 'gold');   -- 20000

-- 프로시저
CREATE OR REPLACE PROCEDURE batch_update()
LANGUAGE plpgsql
AS $$
BEGIN
  FOR i IN 1..100 LOOP
    UPDATE orders SET ... WHERE id_batch = i;
    COMMIT;   -- 매 배치마다 commit
  END LOOP;
END;
$$;

CALL batch_update();

CREATE OR REPLACE FUNCTION trg_audit_orders()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log(table_name, op, user_name, timestamp, row_data)
    VALUES (TG_TABLE_NAME, TG_OP, session_user, now(), to_jsonb(NEW));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION trg_audit_orders();

-- SQL 함수
CREATE FUNCTION get_user(p_id int) RETURNS users AS $$
  SELECT * FROM users WHERE id = p_id LIMIT 1;
$$ LANGUAGE sql STABLE;

-- PL/pgSQL 함수
CREATE FUNCTION get_user_plpgsql(p_id int) RETURNS users AS $$
DECLARE
  rec users;
BEGIN
  SELECT * INTO rec FROM users WHERE id = p_id LIMIT 1;
  RETURN rec;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE FUNCTION log_event(event text) RETURNS void AS $$
  INSERT INTO audit_log(event, session_user, timestamp)
    VALUES (event, session_user, now());
$$ LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, public;

CREATE FUNCTION dynamic_query(p_table text, p_id int)
RETURNS setof users AS $$
BEGIN
  RETURN QUERY EXECUTE
    format('SELECT * FROM %I WHERE id = $1', p_table)
    USING p_id;
END;
$$ LANGUAGE plpgsql;

BEGIN
  INSERT INTO logs(...) VALUES (...);
EXCEPTION
  WHEN unique_violation THEN
    UPDATE logs SET ... WHERE id = ...;
  WHEN OTHERS THEN
    RAISE NOTICE 'Error: %', SQLERRM;
END;

CREATE EXTENSION plpython3u;   -- 슈퍼유저, untrusted

CREATE FUNCTION fetch_external(url text)
RETURNS text AS $$
  import urllib.request
  return urllib.request.urlopen(url).read().decode()
$$ LANGUAGE plpython3u;

RAISE NOTICE 'value = %', some_var;
RAISE WARNING 'attention';
RAISE EXCEPTION 'fatal — abort';

-- 트랜잭션 안 출력
SET client_min_messages TO NOTICE;