Supabase数据库函数 #

数据库函数特点
├── 在数据库服务器执行
├── 支持复杂逻辑
├── 可返回各种数据类型
├── 可被SQL和客户端调用
└── 性能高效

CREATE OR REPLACE FUNCTION function_name(param1 type, param2 type)
RETURNS return_type AS $$
BEGIN
    -- 函数体
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- 返回单个值
CREATE OR REPLACE FUNCTION add(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT add(1, 2);  -- 3

-- 返回多行
CREATE OR REPLACE FUNCTION get_user_posts(user_id UUID)
RETURNS SETOF posts AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM posts
    WHERE author_id = user_id
    ORDER BY created_at DESC;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT * FROM get_user_posts('user-uuid');

CREATE OR REPLACE FUNCTION get_user_stats(user_id UUID)
RETURNS JSON AS $$
DECLARE
    result JSON;
BEGIN
    SELECT json_build_object(
        'posts_count', (SELECT COUNT(*) FROM posts WHERE author_id = user_id),
        'comments_count', (SELECT COUNT(*) FROM comments WHERE user_id = user_id),
        'total_views', (SELECT COALESCE(SUM(view_count), 0) FROM posts WHERE author_id = user_id)
    ) INTO result;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT get_user_stats('user-uuid');

CREATE OR REPLACE FUNCTION example(
    IN in_param TEXT,        -- 输入参数
    OUT out_param TEXT,      -- 输出参数
    INOUT inout_param TEXT   -- 输入输出参数
)
AS $$
BEGIN
    out_param := in_param || ' processed';
    inout_param := inout_param || ' modified';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION greet(
    name TEXT,
    greeting TEXT DEFAULT 'Hello'
)
RETURNS TEXT AS $$
BEGIN
    RETURN greeting || ', ' || name || '!';
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT greet('World');              -- Hello, World!
SELECT greet('World', 'Hi');        -- Hi, World!

CREATE OR REPLACE FUNCTION sum_all(VARIADIC nums NUMERIC[])
RETURNS NUMERIC AS $$
DECLARE
    total NUMERIC := 0;
BEGIN
    SELECT SUM(num) INTO total
    FROM unnest(nums) AS num;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT sum_all(1, 2, 3, 4, 5);  -- 15

CREATE OR REPLACE FUNCTION example()
RETURNS TEXT AS $$
DECLARE
    name TEXT := 'John';
    count INTEGER;
    created_at TIMESTAMPTZ;
    data JSONB;
BEGIN
    count := 10;
    created_at := NOW();
    data := '{"key": "value"}'::jsonb;
    
    RETURN name;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_grade(score INTEGER)
RETURNS TEXT 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;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION generate_series_custom(n INTEGER)
RETURNS SETOF INTEGER AS $$
BEGIN
    FOR i IN 1..n LOOP
        RETURN NEXT i;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- WHILE循环
CREATE OR REPLACE FUNCTION while_example()
RETURNS INTEGER AS $$
DECLARE
    i INTEGER := 0;
    total INTEGER := 0;
BEGIN
    WHILE i < 10 LOOP
        total := total + i;
        i := i + 1;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

// 调用无参数函数
const { data, error } = await supabase.rpc('get_all_users')

// 调用带参数函数
const { data, error } = await supabase.rpc('get_user_posts', {
  user_id: 'user-uuid'
})

// 调用返回JSON的函数
const { data, error } = await supabase.rpc('get_user_stats', {
  user_id: 'user-uuid'
})

const { data, error } = await supabase.rpc('get_user_posts', {
  user_id: userId
})

if (error) {
  console.error('Error:', error)
} else {
  // data 是 posts 数组
  console.log('Posts:', data)
}

-- 以函数定义者权限执行
CREATE OR REPLACE FUNCTION admin_function()
RETURNS TEXT AS $$
BEGIN
    -- 可以访问定义者有权限的所有数据
    RETURN 'Admin operation completed';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 以调用者权限执行（默认）
CREATE OR REPLACE FUNCTION user_function()
RETURNS TEXT AS $$
BEGIN
    -- 只能访问调用者有权限的数据
    RETURN 'User operation completed';
END;
$$ LANGUAGE plpgsql SECURITY INVOKER;

CREATE OR REPLACE FUNCTION search_posts(search_query TEXT)
RETURNS SETOF posts AS $$
BEGIN
    RETURN QUERY
    SELECT *
    FROM posts
    WHERE 
        title ILIKE '%' || search_query || '%' OR
        content ILIKE '%' || search_query || '%'
    ORDER BY created_at DESC;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION paginate_posts(
    page_num INTEGER DEFAULT 1,
    page_size INTEGER DEFAULT 10
)
RETURNS TABLE(
    id BIGINT,
    title TEXT,
    total_count BIGINT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        p.id,
        p.title,
        COUNT(*) OVER() AS total_count
    FROM posts p
    ORDER BY p.created_at DESC
    LIMIT page_size
    OFFSET (page_num - 1) * page_size;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION bulk_insert_products(
    products JSONB
)
RETURNS INTEGER AS $$
DECLARE
    inserted_count INTEGER;
BEGIN
    INSERT INTO products (name, price, category)
    SELECT 
        p->>'name',
        (p->>'price')::NUMERIC,
        p->>'category'
    FROM jsonb_array_elements(products) AS p;
    
    GET DIAGNOSTICS inserted_count = ROW_COUNT;
    
    RETURN inserted_count;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION safe_operation()
RETURNS TEXT AS $$
BEGIN
    BEGIN
        -- 可能出错的操作
        INSERT INTO users (email) VALUES ('test@example.com');
        RETURN 'Success';
    EXCEPTION
        WHEN unique_violation THEN
            RETURN 'Email already exists';
        WHEN others THEN
            RETURN 'Error: ' || SQLERRM;
    END;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION transfer_funds(
    from_id UUID,
    to_id UUID,
    amount NUMERIC
)
RETURNS TEXT AS $$
DECLARE
    balance NUMERIC;
BEGIN
    SELECT account_balance INTO balance
    FROM accounts WHERE id = from_id;
    
    IF balance < amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;
    
    UPDATE accounts SET account_balance = account_balance - amount
    WHERE id = from_id;
    
    UPDATE accounts SET account_balance = account_balance + amount
    WHERE id = to_id;
    
    RETURN 'Transfer completed';
END;
$$ LANGUAGE plpgsql;