Supabase dynamic RPC function

I am looking to create an RPC for grabbing distinct values of a column but I am going to be doing this across multiple tables and I don't want to have to write this function over and over for each column and each table. I have the following SQL statement that does what I need:

SELECT owner
FROM customers 
GROUP BY owner 
ORDER BY owner Asc;

I would like to make the owner and customer parameters. I see how to add "arguments" in an RPC but I am not sure how to use them as a column/table for my issue above.

enter image description here

Does anyone know how to handle this in a supabase function?

Bonus question:

How would I go about doing this for a join? I have the following based on the accepted answer but it is failing with 'column "customers.owner_id" does not exist' I have hardcoded this function and it is working as expected just not with the parameterized version below:

CREATE OR REPLACE FUNCTION fetch_autocomplete_options(
  table_name text,
  join_table_name text,
  join_column_name text,
  table_column_name text,
  join_table_column_name text
)
RETURNS TABLE ( value text) AS 
$BODY$
  BEGIN
      RETURN QUERY EXECUTE format(
        'SELECT DISTINCT %I::text FROM %I INNER JOIN %I ON %I=%I ORDER BY %I ASC;',
        join_column_name, table_name, join_table_name, table_column_name, join_table_column_name, join_column_name
      );
  END;
$BODY$
LANGUAGE plpgsql;


Comments

Popular posts from this blog

Spring Elasticsearch Operations

Network Error and Timeout on Authorize.net JS

Object oriented programming concepts (OOPs)