存储过程

-- 创建函数
CREATE OR REPLACE FUNCTION get_user_posts(user_id INT)
RETURNS TABLE(title VARCHAR, created_at TIMESTAMP) AS $$
BEGIN
    RETURN QUERY
    SELECT p.title, p.created_at
    FROM posts p
    WHERE p.user_id = get_user_posts.user_id
    ORDER BY p.created_at DESC;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT * FROM get_user_posts(1);

-- 计算两数之和
CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT)
RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT add_numbers(10, 20);  -- 结果：30

CREATE OR REPLACE FUNCTION search_products(keyword VARCHAR)
RETURNS TABLE(id INT, name VARCHAR, price DECIMAL) AS $$
BEGIN
    RETURN QUERY
    SELECT p.id, p.name, p.price
    FROM products p
    WHERE p.name ILIKE '%' || keyword || '%';
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT * FROM search_products('phone');

CREATE OR REPLACE FUNCTION get_user_stats(
    user_id INT,
    OUT post_count INT,
    OUT comment_count INT
) AS $$
BEGIN
    SELECT COUNT(*) INTO post_count FROM posts WHERE user_id = user_id;
    SELECT COUNT(*) INTO comment_count FROM comments WHERE user_id = user_id;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT * FROM get_user_stats(1);

CREATE OR REPLACE FUNCTION check_stock(product_id INT, quantity INT)
RETURNS TEXT AS $$
DECLARE
    current_stock INT;
BEGIN
    SELECT stock INTO current_stock FROM products WHERE id = product_id;
    
    IF current_stock >= quantity THEN
        RETURN '库存充足';
    ELSIF current_stock > 0 THEN
        RETURN '库存不足';
    ELSE
        RETURN '无库存';
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION generate_sequence(n INT)
RETURNS SETOF INT AS $$
DECLARE
    i INT := 1;
BEGIN
    LOOP
        EXIT WHEN i > n;
        RETURN NEXT i;
        i := i + 1;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sum_to_n(n INT)
RETURNS INT AS $$
DECLARE
    total INT := 0;
    i INT;
BEGIN
    FOR i IN 1..n LOOP
        total := total + i;
    END LOOP;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION transfer_money(
    from_user INT,
    to_user INT,
    amount DECIMAL
) RETURNS BOOLEAN AS $$
DECLARE
    from_balance DECIMAL;
BEGIN
    -- 检查余额
    SELECT balance INTO from_balance 
    FROM accounts WHERE user_id = from_user;
    
    IF from_balance < amount THEN
        RAISE EXCEPTION '余额不足';
    END IF;
    
    -- 转账
    UPDATE accounts SET balance = balance - amount 
    WHERE user_id = from_user;
    
    UPDATE accounts SET balance = balance + amount 
    WHERE user_id = to_user;
    
    -- 记录日志
    INSERT INTO transfer_logs (from_user, to_user, amount, created_at)
    VALUES (from_user, to_user, amount, NOW());
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT transfer_money(1, 2, 100.00);