Using GiST Notation from LTREE Extension in Supabase Client

[alcaidio]

I'm working with the LTREE extension in my PostgreSQL database hosted on Supabase, and I need to utilize the GiST (Generalized Search Tree) notation provided by the extension. Specifically, I want to perform queries using operators such as <, >=, @>, etc...

I'm aware that I can create custom functions and call them using supabase.rpc(fn), but this approach is redundant. Is there a way to execute custom SQL queries directly from the Supabase client or use GiST notation ?

I've explored the Supabase documentation and haven't found a straightforward solution for this. Any guidance on how to use GiST notation with LTREE extension in Supabase client would be greatly appreciated.

Thank you!

[alcaidio]

I find a workaround, by creating a function to pass sql query 😁 only in tree table... Impossible to pass the table, why???

In supabase database :

CREATE FUNCTION sqlOnTree(query TEXT)
RETURNS SETOF tree
AS $$
BEGIN
    RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql;

In my web app :
supabase.rpc('sqlOnTree', { query: "select * from tree where 'A.B' @> path" }).returns<Tables<'tree'>[]>()