A new feature that execute sql by using supabase-py

[justino2m]

I attempted to utilize the Supabase-py query builder for my application, but it did not provide enough functionality for my query. Specifically, it lacked support for complex where clauses. I required the ability to implement multiple and/or combined conditions, which I was unable to achieve using Supabase-py. As a result, I have decided to execute SQL queries directly. Is there a solution available to execute SQL queries directly instead?

For example,
select * from tbl_name where col1=1 and (col2=2 or col2=1 and flag=1)

supabase-py can't implement this query
I tried to use .or_, .filter functions

Besides this example, there are complex SQL queries that are required in the application.
Thank you

[silentworks]

For these instance you should use a PostgreSQL function along with .rpc method on the supabase-py library.

[GaryAustin1]

You would write your sql in a postgres function and then call it with the rpc call.
You probably can do the example query you show as the .or() takes and in it. Also when you chain filters one after the other they are anded.
https://postgrest.org/en/v12/references/api/tables_views.html#logical-operators

[justino2m]

I have built my sql function and run it

CREATE OR REPLACE FUNCTION execute_raw_select_sql(query text)
RETURNS SETOF RECORD AS $$
DECLARE
    forbidden_pattern text[] := ARRAY['INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'ALTER', 'DROP', 'CREATE', 'REVOKE', 'GRANT'];
BEGIN
    -- IF EXISTS (SELECT 1 FROM unnest(forbidden_pattern) pattern WHERE upper(query) LIKE '%' || upper(pattern) || '%') THEN
    --     RAISE EXCEPTION 'Query contains forbidden operations';
    -- END IF;
    -- TODO: the if is broken somehow

    RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql;

Getting this

{
  code: '0A000',
  details: null,
  hint: null,
  message: 'materialize mode required, but it is not allowed in this context'
}

Any help appreciated.

[silentworks]

Please don't do this at all, it is a bad idea. Your app will be open to all SQL queries which isn't something you should do. Write a function for the specific query you want to run and not a generic SQL function that takes any SQL as an argument.

[justino2m]

Certainly, this is a simple example. There are validations for delete, insert, drop, and other operations.

What I would like to know is if Supabase does not allow the use of RETURNS SETOF RECORD AS $$ in function execution. Is there a solution for this?

[GaryAustin1]

Supabase does not impact your Postgres code running. Setof Record and Table returns work fine.