This repository has no description
1-- Create a function to execute raw SQL queries safely
2-- This allows direct SQL execution for better performance and reliability
3
4CREATE OR REPLACE FUNCTION execute_raw_query(raw_query TEXT)
5RETURNS JSONB
6LANGUAGE plpgsql
7SECURITY DEFINER -- This runs with the privileges of the function creator
8AS $$
9DECLARE
10 result JSONB;
11BEGIN
12 -- Only allow SELECT queries for security
13 IF position('SELECT' in upper(raw_query)) != 1 THEN
14 RAISE EXCEPTION 'Only SELECT queries are allowed';
15 END IF;
16
17 -- Execute the query and convert result to JSON
18 EXECUTE 'SELECT json_agg(t) FROM (' || raw_query || ') t' INTO result;
19
20 -- Return empty array instead of null if no results
21 RETURN COALESCE(result, '[]'::jsonb);
22END;
23$$;
24
25-- Test the function
26SELECT execute_raw_query('SELECT id, did, handle, text FROM flushing_records ORDER BY id DESC LIMIT 3');