Custom SQL Query Execution in Postgrest

custom_sql_query_postgres.md

Postgrest doesn't like you executing arbitrary queries, but you can get around it by defining a function that executes the query for you:

$ psql mydb
mydb=# create function custom_query(query text) returns setof json as $f$
    begin 
    return query execute format('with tmp as (%s) select row_to_json(tmp.*) from tmp;', query); 
    end
    $f$ language plpgsql;
CREATE FUNCTION

You can then execute arbitrary queries by POST'ing to this function:

$ curl -XPOST -H "Content-Type: application/json" http://127.0.0.1:3002/rpc/custom_query \
    -d -d '{"query": "select date_trunc($$month$$, \"Date\") as month, avg(\"Quality\") as quality from sleep group by 1"}'

Voila! Custom queries through Postgrest.

How to do the same with prepared queries to prevent SQL injections?

CREATE OR REPLACE FUNCTION custom_query(query TEXT, params_as_json text) RETURNS SETOF json AS $$
    BEGIN
	    EXECUTE format('prepare query (json) AS WITH tmp as (%s) select row_to_json(tmp.*) from tmp', query);
	    RETURN QUERY EXECUTE format('execute query(%L)', params_as_json);
	    DEALLOCATE query;
    END
    $$ LANGUAGE plpgsql;

Example query:

SELECT custom_query(
  $$SELECT hashid, ST_AsGeoJSON(loc) as loc FROM points WHERE hashid = $1::json->>'id'$$,
  '{"id": "BkaLrxXvYx"}')

I've tried my best to prevent SQL injection... But beware. You really need to make sure JSON is passed. Maybe it's worth it to change it to json type for a slight performance hit (need to deserialize and serialize one time extra).

Thank you guys for awesome responses, I added the json params code:

CREATE OR REPLACE FUNCTION sql(query TEXT, params json) 
RETURNS SETOF json AS $$
BEGIN
	EXECUTE format('prepare query (json) AS WITH tmp as (%s) select row_to_json(tmp.*) from tmp', query);
	RETURN QUERY EXECUTE format('execute query(%L)', params);
	DEALLOCATE query;
END
 $$ LANGUAGE plpgsql;

From supabase it works this way:
Request (in local supabase)

curl -X POST \
  -H 'Content-Type: application/json' \
  -d $'{
        "query": "SELECT oid, datname FROM pg_database WHERE datname ilike $1->>\'q\'",
        "params": {
          "q": "%pos%"
        }
      }' \
  http://localhost:54321/rest/v1/rpc/sql

Response

[
  {
    "oid": "5",
    "datname": "postgres"
  }
]