พื้นฐานฟังก์ชัน PL/pgSQL

PL/pgSQL คืออะไร

โครงร่างฟังก์ชัน

CREATE OR REPLACE FUNCTION add(a INT, b INT)
RETURNS INT AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT add(2, 3);   -- 5

ตัวแปร

CREATE FUNCTION user_orders(uid BIGINT)
RETURNS INT AS $$
DECLARE
  cnt INT;
BEGIN
  SELECT COUNT(*) INTO cnt FROM orders WHERE user_id = uid;
  RETURN cnt;
END;
$$ LANGUAGE plpgsql STABLE;

การควบคุมลำดับการทำงาน

IF cnt = 0 THEN
  RAISE NOTICE 'no orders';
ELSIF cnt < 10 THEN
  RAISE NOTICE 'few';
ELSE
  RAISE NOTICE 'many';
END IF;

FOR i IN 1..10 LOOP
  RAISE NOTICE '%', i;
END LOOP;

วนซ้ำผ่านผลลัพธ์ของ query

CREATE FUNCTION recalc_totals() RETURNS VOID AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT id FROM users LOOP
    UPDATE users SET
      order_count = (SELECT COUNT(*) FROM orders WHERE user_id = r.id)
    WHERE id = r.id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

RETURNS TABLE

CREATE FUNCTION top_buyers(n INT)
RETURNS TABLE (user_id BIGINT, total NUMERIC) AS $$
BEGIN
  RETURN QUERY
    SELECT o.user_id, SUM(o.total)
    FROM orders o
    GROUP BY o.user_id
    ORDER BY SUM(o.total) DESC
    LIMIT n;
END;
$$ LANGUAGE plpgsql STABLE;

SELECT * FROM top_buyers(10);

พารามิเตอร์ IN / OUT / INOUT

CREATE FUNCTION stats(uid BIGINT,
  OUT total NUMERIC,
  OUT cnt INT) AS $$
BEGIN
  SELECT SUM(total), COUNT(*) INTO total, cnt
  FROM orders WHERE user_id = uid;
END;
$$ LANGUAGE plpgsql STABLE;

SELECT * FROM stats(42);   -- total | cnt

การจัดการข้อยกเว้น

BEGIN
  INSERT INTO users (email) VALUES ($1);
EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'email already exists';
  WHEN OTHERS THEN
    RAISE;
END;

RAISE สำหรับการบันทึกข้อมูล

RAISE NOTICE 'processed % rows', cnt;
RAISE EXCEPTION 'bad state: %', state USING ERRCODE = 'check_violation';

เครื่องหมายบอกความแปรผัน

SECURITY DEFINER

CREATE FUNCTION sensitive_op() RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$ ... $$;

-- Always set search_path explicitly to avoid hijacking:
ALTER FUNCTION sensitive_op() SET search_path = public, pg_temp;

สรุป

ตรวจสอบอย่างรวดเร็ว