Complex query with parameters in SUPABASE

Question

I would like to create this except query in supabase, but with parameters in the day column. For example:

CREATE VIEW employees_without_registering AS 
  SELECT employees.id
  FROM employees
  EXCEPT
  SELECT "idEmployee" FROM registers WHERE "day"='2021-07-25'

What I tried was to create a view in the query editor and then try to access the results with something like this:

await supabase.from('employees_without_registering')
  .select('*');

but the results are fixed to that query only. I would like to change the day according to whatever I choose.

Maybe my approach is bad and this is solved with another way with the supabase api, but I would to hear suggestions on how to solve this problem.

Answer 1

you will need to write a stored function for that and call it with supabase.rpc("get_my_complex_query",{parameter:1})

go to the SQL query editor on the dashboard and:

drop function if exists get_my_complex_query;
create function get_my_complex_query (parameter int)
RETURNS TABLE(column1 integer, column2 varchar) AS
$$
BEGIN
  return query SELECT column1, column2 FROM "TableName1"
    INNER JOIN "TableName2" ON "TableName1".column = "TableName2".column 
    WHERE "Table2".column = parameter;
END;
$$
language plpgsql volatile;

here are a few useful links for further details:

https://www.postgresqltutorial.com/introduction-to-postgresql-stored-procedures/

https://www.postgresql.org/docs/9.1/plpgsql-declarations.html

https://www.postgresqltutorial.com/plpgsql-function-returns-a-table/

your other option is to filter your view on the client side:

await supabase.from('employees')
  .select('id')
  .neq("day","2021-07-25");