توابع PL/pgSQL و Procedures

PL/pgSQL زبان procedural داخلی PostgreSQL است که به ما اجازه می‌دهد توابع، stored procedures و triggers پیچیده با logic کامل بنویسیم. در این فصل با ساختار توابع، control flow، exception handling و الگوهای حرفه‌ای آشنا می‌شویم.

چرا PL/pgSQL؟

• کد business logic نزدیک به داده – performance بالاتر
• یک‌بار write، چندین بار اجرا
• encapsulation – logic در یک جا متمرکز
• کاهش round-trip بین app و دیتابیس
• پشتیبانی از تراکنش، exception، loop

تابع ساده

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a + b;
END;
$$;

-- استفاده
SELECT add_numbers(5, 3);    -- 8

تابع با چند خروجی

CREATE OR REPLACE FUNCTION divide_with_remainder(
    a INTEGER, b INTEGER,
    OUT quotient INTEGER, OUT remainder INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF b = 0 THEN
        RAISE EXCEPTION 'تقسیم بر صفر مجاز نیست';
    END IF;
    quotient := a / b;
    remainder := a % b;
END;
$$;

SELECT * FROM divide_with_remainder(17, 5);
-- quotient | remainder
-- ---------+-----------
--        3 |         2

متغیرها

CREATE OR REPLACE FUNCTION calculate_total(order_id INTEGER)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    -- متغیرها
    v_subtotal NUMERIC := 0;
    v_tax_rate NUMERIC := 0.09;
    v_total NUMERIC;
    v_currency TEXT := 'IRR';
    v_user_id INTEGER;
    v_record orders%ROWTYPE;        -- نوع کل ردیف جدول
BEGIN
    -- خواندن از جدول
    SELECT subtotal, user_id 
    INTO v_subtotal, v_user_id
    FROM orders 
    WHERE id = order_id;
    
    -- محاسبه
    v_total := v_subtotal * (1 + v_tax_rate);
    
    RETURN v_total;
END;
$$;

%TYPE و %ROWTYPE

CREATE OR REPLACE FUNCTION get_user_info(user_id INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_email users.email%TYPE;          -- همان نوع ستون
    v_user_record users%ROWTYPE;       -- کل ردیف
BEGIN
    SELECT * INTO v_user_record FROM users WHERE id = user_id;
    RETURN v_user_record.email || ' - ' || v_user_record.username;
END;
$$;

Control Flow

IF / ELSIF / ELSE

CREATE OR REPLACE FUNCTION grade_score(score INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    IF score >= 90 THEN
        RETURN 'A';
    ELSIF score >= 80 THEN
        RETURN 'B';
    ELSIF score >= 70 THEN
        RETURN 'C';
    ELSIF score >= 60 THEN
        RETURN 'D';
    ELSE
        RETURN 'F';
    END IF;
END;
$$;

CASE

CREATE OR REPLACE FUNCTION translate_status(status TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN CASE status
        WHEN 'pending' THEN 'در انتظار'
        WHEN 'processing' THEN 'در حال پردازش'
        WHEN 'shipped' THEN 'ارسال‌شده'
        WHEN 'delivered' THEN 'تحویل‌شده'
        WHEN 'cancelled' THEN 'لغو‌شده'
        ELSE 'نامشخص'
    END;
END;
$$;

حلقه‌ها

LOOP / EXIT

CREATE OR REPLACE FUNCTION sum_to_n(n INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_sum INTEGER := 0;
    v_i INTEGER := 1;
BEGIN
    LOOP
        EXIT WHEN v_i > n;
        v_sum := v_sum + v_i;
        v_i := v_i + 1;
    END LOOP;
    RETURN v_sum;
END;
$$;

SELECT sum_to_n(100);    -- 5050

WHILE

DECLARE
    v_counter INTEGER := 0;
BEGIN
    WHILE v_counter < 10 LOOP
        v_counter := v_counter + 1;
        RAISE NOTICE 'مقدار: %', v_counter;
    END LOOP;
END;

FOR loop

-- بازه عددی
FOR i IN 1..10 LOOP
    RAISE NOTICE '%', i;
END LOOP;

-- معکوس
FOR i IN REVERSE 10..1 LOOP
    RAISE NOTICE '%', i;
END LOOP;

-- step
FOR i IN 1..20 BY 2 LOOP
    RAISE NOTICE '%', i;
END LOOP;

FOR… IN SELECT – حلقه روی query

CREATE OR REPLACE FUNCTION process_orders()
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_count INTEGER := 0;
    v_order RECORD;            -- نوع پویا
BEGIN
    FOR v_order IN 
        SELECT id, user_id, total FROM orders WHERE status = 'pending'
    LOOP
        -- پردازش هر سفارش
        UPDATE orders SET status = 'processing' WHERE id = v_order.id;
        v_count := v_count + 1;
        
        RAISE NOTICE 'پردازش سفارش %: کاربر % مبلغ %', 
            v_order.id, v_order.user_id, v_order.total;
    END LOOP;
    
    RETURN v_count;
END;
$$;

RETURNS TABLE – برگرداندن جدول

CREATE OR REPLACE FUNCTION get_top_customers(min_total NUMERIC DEFAULT 1000000)
RETURNS TABLE (
    customer_id INTEGER,
    customer_name TEXT,
    total_spent NUMERIC,
    order_count BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        u.id, 
        u.username,
        COALESCE(SUM(o.total), 0),
        COUNT(o.id)
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
    GROUP BY u.id, u.username
    HAVING COALESCE(SUM(o.total), 0) >= min_total
    ORDER BY 3 DESC;
END;
$$;

-- استفاده
SELECT * FROM get_top_customers(5000000);
SELECT * FROM get_top_customers();    -- با default

RETURNS SETOF

CREATE OR REPLACE FUNCTION active_users()
RETURNS SETOF users          -- مجموعه‌ای از ردیف‌های users
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY 
    SELECT * FROM users WHERE last_login > NOW() - INTERVAL '30 days';
END;
$$;

SELECT * FROM active_users() WHERE id < 100;

Exception Handling

CREATE OR REPLACE FUNCTION transfer_money(
    from_account INTEGER,
    to_account INTEGER,
    amount NUMERIC
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
    v_from_balance NUMERIC;
BEGIN
    -- شروع تراکنش - PostgreSQL خودش transaction می‌سازد
    
    SELECT balance INTO v_from_balance 
    FROM accounts WHERE id = from_account FOR UPDATE;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'حساب مبدأ یافت نشد';
    END IF;
    
    IF v_from_balance < amount THEN
        RAISE EXCEPTION 'موجودی کافی نیست (موجود: %, نیاز: %)', 
            v_from_balance, amount
            USING ERRCODE = 'insufficient_privilege';
    END IF;
    
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    
    INSERT INTO transactions(from_id, to_id, amount, status) 
    VALUES (from_account, to_account, amount, 'completed');
    
    RETURN TRUE;

EXCEPTION
    WHEN no_data_found THEN
        RAISE NOTICE 'داده پیدا نشد';
        RETURN FALSE;
    WHEN unique_violation THEN
        RAISE NOTICE 'تکراری: %', SQLERRM;
        RETURN FALSE;
    WHEN OTHERS THEN
        RAISE NOTICE 'خطای ناشناخته: % %', SQLSTATE, SQLERRM;
        -- می‌توان خطا را propagate کرد:
        RAISE;
END;
$$;

کدهای خطای رایج

RAISE – پیام و خطا

-- سطوح:
RAISE DEBUG 'متن دیباگ: %', value;       -- در لاگ
RAISE LOG 'متن لاگ: %', value;           
RAISE INFO 'اطلاعات: %', value;          
RAISE NOTICE 'هشدار: %', value;          -- معمولاً برای client
RAISE WARNING 'اخطار: %', value;         
RAISE EXCEPTION 'خطا: %', value;         -- متوقف کردن

-- با ERRCODE
RAISE EXCEPTION 'مقدار نامعتبر %', x
    USING ERRCODE = 'invalid_parameter_value',
          DETAIL = 'مقدار باید مثبت باشد',
          HINT = 'لطفاً مقدار را بررسی کنید';

SQL پویا

CREATE OR REPLACE FUNCTION search_table(
    table_name TEXT,
    column_name TEXT,
    search_value TEXT
)
RETURNS TABLE(id INTEGER, value TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT id, %I::TEXT FROM %I WHERE %I = $1',
        column_name, table_name, column_name
    ) USING search_value;
END;
$$;

-- format() دیتاتایپ‌ها را امن escape می‌کند
-- %I = identifier (نام جدول/ستون)
-- %L = literal (با quote)
-- %s = string ساده

-- جلوگیری از SQL Injection
EXECUTE 'SELECT * FROM users WHERE name = ' || quote_literal(user_input);
-- یا بهتر:
EXECUTE 'SELECT * FROM users WHERE name = $1' USING user_input;

Stored Procedures

PostgreSQL 11+ از PROCEDURE هم پشتیبانی می‌کند که می‌تواند COMMIT/ROLLBACK داخلی داشته باشد:

CREATE OR REPLACE PROCEDURE batch_update_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    v_batch_size INTEGER := 1000;
    v_updated INTEGER;
BEGIN
    LOOP
        UPDATE orders 
        SET processed_at = NOW()
        WHERE id IN (
            SELECT id FROM orders 
            WHERE processed_at IS NULL 
            LIMIT v_batch_size
        );
        
        GET DIAGNOSTICS v_updated = ROW_COUNT;
        EXIT WHEN v_updated = 0;
        
        COMMIT;            -- procedure می‌تواند commit کند، function نمی‌تواند
        RAISE NOTICE 'بروزرسانی شد: %', v_updated;
    END LOOP;
END;
$$;

-- فراخوانی
CALL batch_update_orders();

مثال‌های کاربردی

۱. تابع محاسبه سن

CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE)
RETURNS INTEGER
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(birth_date))::INTEGER;
END;
$$;

SELECT calculate_age('1990-05-15');    -- 35

۲. تابع تبدیل تاریخ شمسی

CREATE OR REPLACE FUNCTION gregorian_to_jalali(g_date DATE)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
    g_year INTEGER := EXTRACT(YEAR FROM g_date);
    g_month INTEGER := EXTRACT(MONTH FROM g_date);
    g_day INTEGER := EXTRACT(DAY FROM g_date);
    j_year INTEGER;
    -- ... منطق تبدیل
BEGIN
    -- (الگوریتم کامل را می‌توانید از کتابخانه‌های موجود کپی کنید)
    -- این یک نمونه ساده است
    RETURN format('%s/%s/%s', j_year, g_month, g_day);
END;
$$;

۳. تابع pagination

CREATE OR REPLACE FUNCTION paginate_products(
    p_page INTEGER DEFAULT 1,
    p_size INTEGER DEFAULT 20,
    p_search TEXT DEFAULT NULL
)
RETURNS TABLE (
    id INTEGER,
    name TEXT,
    price NUMERIC,
    total_count BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH filtered AS (
        SELECT p.id, p.name, p.price
        FROM products p
        WHERE p_search IS NULL OR p.name ILIKE '%' || p_search || '%'
    ),
    counted AS (
        SELECT COUNT(*) AS total FROM filtered
    )
    SELECT f.id, f.name, f.price, c.total
    FROM filtered f, counted c
    ORDER BY f.id
    LIMIT p_size OFFSET (p_page - 1) * p_size;
END;
$$;

SELECT * FROM paginate_products(1, 10, 'فرش');

Volatility – بهینه‌سازی

PostgreSQL سه سطح volatility دارد:

• VOLATILE: پیش‌فرض – هر بار اجرا می‌شود
• STABLE: در یک query ثابت – مثل CURRENT_DATE
• IMMUTABLE: همیشه با همان ورودی، همان خروجی – cacheable

-- IMMUTABLE - planner می‌تواند کش کند
CREATE FUNCTION square(n INTEGER) 
RETURNS INTEGER 
IMMUTABLE 
LANGUAGE plpgsql 
AS $$ BEGIN RETURN n*n; END; $$;

-- IMMUTABLE اجازه می‌دهد روی expression index بسازیم
CREATE INDEX idx_squares ON numbers(square(value));

SECURITY DEFINER vs INVOKER

-- پیش‌فرض - با حقوق فراخواننده اجرا می‌شود
CREATE FUNCTION my_func() RETURNS VOID AS $$ ... $$ 
LANGUAGE plpgsql SECURITY INVOKER;

-- با حقوق سازنده تابع - مفید برای دسترسی محدود
CREATE FUNCTION admin_action() RETURNS VOID AS $$ ... $$ 
LANGUAGE plpgsql SECURITY DEFINER;

-- مثال: کاربر معمولی نمی‌تواند مستقیم به جدول log بنویسد، اما می‌تواند تابع را صدا بزند
GRANT EXECUTE ON FUNCTION log_event TO public;

مدیریت توابع

-- لیست توابع
df

-- جزئیات یک تابع
df+ my_function

-- منبع تابع
SELECT prosrc FROM pg_proc WHERE proname = 'my_function';

-- حذف
DROP FUNCTION my_function(INTEGER);
DROP FUNCTION IF EXISTS my_function(INTEGER, TEXT);

-- اجازه اجرا
GRANT EXECUTE ON FUNCTION my_function TO some_user;
REVOKE EXECUTE ON FUNCTION my_function FROM PUBLIC;

بهترین شیوه‌ها

• منطق ساده در SQL، منطق پیچیده در PL/pgSQL
• منطق business بسیار پیچیده در application layer
• STABLE و IMMUTABLE برای performance
• EXCEPTION handling با dataset‌های بزرگ هزینه دارد
• format() با %I و %L به‌جای string concatenation
• SECURITY DEFINER فقط در صورت نیاز و با احتیاط
• تست کردن با pgTAP یا fixture‌های manual

جمع‌بندی

• PL/pgSQL برای logic procedural در دیتابیس
• FUNCTION (با RETURN) و PROCEDURE (با CALL)
• متغیرها، control flow، exception handling
• RETURNS TABLE و SETOF برای برگرداندن مجموعه
• Dynamic SQL با format() امن
• Volatility برای بهینه‌سازی
• SECURITY DEFINER برای دسترسی کنترل‌شده