85 lines
2.1 KiB
PL/PgSQL
85 lines
2.1 KiB
PL/PgSQL
-- Supabase RPC Functions for Raw SQL Execution
|
|
-- Run this in your Supabase SQL Editor to enable rawQuery() functionality
|
|
|
|
-- Function to execute parameterized SQL queries
|
|
-- WARNING: This function uses SECURITY DEFINER which runs with elevated privileges
|
|
-- Only grant execute permission to trusted roles
|
|
CREATE OR REPLACE FUNCTION exec_sql(
|
|
query_text text,
|
|
params jsonb DEFAULT '[]'::jsonb
|
|
)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
DECLARE
|
|
result jsonb;
|
|
param_array text[];
|
|
formatted_query text;
|
|
BEGIN
|
|
-- Convert JSONB params to text array
|
|
SELECT array_agg(value::text)
|
|
INTO param_array
|
|
FROM jsonb_array_elements(params);
|
|
|
|
-- Format query with parameters
|
|
-- Note: This is a simplified version. For production, use proper parameter binding
|
|
formatted_query := query_text;
|
|
|
|
-- Execute query using dynamic SQL
|
|
-- For SELECT queries, return results as JSON
|
|
IF query_text ILIKE 'SELECT%' THEN
|
|
EXECUTE (
|
|
'SELECT jsonb_agg(row_to_json(t)) FROM (' || query_text || ') t'
|
|
) INTO result USING param_array;
|
|
ELSE
|
|
-- For other queries, execute and return affected rows
|
|
EXECUTE query_text INTO result USING param_array;
|
|
END IF;
|
|
|
|
RETURN COALESCE(result, '[]'::jsonb);
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
RETURN jsonb_build_object(
|
|
'error', true,
|
|
'message', SQLERRM
|
|
);
|
|
END;
|
|
$$;
|
|
|
|
-- Grant execute permission (adjust role as needed)
|
|
-- GRANT EXECUTE ON FUNCTION exec_sql(text, jsonb) TO authenticated;
|
|
-- GRANT EXECUTE ON FUNCTION exec_sql(text, jsonb) TO service_role;
|
|
|
|
-- Alternative: Simpler function for SELECT queries only
|
|
CREATE OR REPLACE FUNCTION exec_select(
|
|
query_text text,
|
|
params jsonb DEFAULT '[]'::jsonb
|
|
)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
DECLARE
|
|
result jsonb;
|
|
BEGIN
|
|
-- Execute SELECT and return as JSONB array
|
|
EXECUTE (
|
|
'SELECT COALESCE(jsonb_agg(row_to_json(t)), ''[]''::jsonb) FROM (' ||
|
|
query_text ||
|
|
') t'
|
|
) INTO result;
|
|
|
|
RETURN result;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
RETURN jsonb_build_object(
|
|
'error', true,
|
|
'message', SQLERRM
|
|
);
|
|
END;
|
|
$$;
|
|
|