Functions (Fonksiyonlar)

Function Nedir?

Fonksiyonlar, tekrar kullanılabilir kod bloklarıdır. İş mantığını veritabanı seviyesinde kapsüllerler.

Basit Fonksiyon

CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM users WHERE status = 'active');
END;
$$ LANGUAGE plpgsql;

-- Kullanım
SELECT get_user_count();

Parametreli Fonksiyon

CREATE OR REPLACE FUNCTION calculate_discount(
    original_price NUMERIC,
    discount_percent INTEGER DEFAULT 10
)
RETURNS NUMERIC AS $$
BEGIN
    RETURN ROUND(original_price * (1 - discount_percent / 100.0), 2);
END;
$$ LANGUAGE plpgsql;

-- Kullanımlar
SELECT calculate_discount(1000, 20);   -- 800.00
SELECT calculate_discount(1000);       -- 900.00 (varsayılan %10)
SELECT calculate_discount(price, 15) AS discounted_price FROM products;

Tablo Döndüren Fonksiyon

CREATE OR REPLACE FUNCTION get_top_customers(limit_count INTEGER DEFAULT 10)
RETURNS TABLE(
    user_id INTEGER,
    username VARCHAR,
    email VARCHAR,
    total_spent NUMERIC,
    order_count BIGINT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        u.id,
        u.username,
        u.email,
        SUM(o.total_amount),
        COUNT(o.id)
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE o.status = 'completed'
    GROUP BY u.id, u.username, u.email
    ORDER BY SUM(o.total_amount) DESC
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

-- Kullanım
SELECT * FROM get_top_customers(5);

SETOF ile Mevcut Tablo Tipi Döndürme

CREATE OR REPLACE FUNCTION get_active_products()
RETURNS SETOF products AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM products WHERE is_active = TRUE AND stock > 0;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_active_products() WHERE price < 1000;

OUT Parametreleri

CREATE OR REPLACE FUNCTION get_order_stats(
    user_id_param INTEGER,
    OUT total_orders INTEGER,
    OUT total_spent NUMERIC,
    OUT avg_order NUMERIC
) AS $$
BEGIN
    SELECT 
        COUNT(*),
        COALESCE(SUM(total_amount), 0),
        COALESCE(ROUND(AVG(total_amount), 2), 0)
    INTO total_orders, total_spent, avg_order
    FROM orders
    WHERE user_id = user_id_param AND status = 'completed';
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_order_stats(5);

Exception Handling

CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF b = 0 THEN
        RAISE NOTICE 'Division by zero, returning NULL';
        RETURN NULL;
    END IF;
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE 'Error: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Fonksiyon Yönetimi

-- Fonksiyonları listele
SELECT proname, prosrc FROM pg_proc WHERE proname LIKE 'get_%';

-- Fonksiyon sil
DROP FUNCTION IF EXISTS get_user_count();
DROP FUNCTION IF EXISTS calculate_discount(NUMERIC, INTEGER);