CODEWITHSUNDEEP
sqlsupabasesupabase-database
Craig Howell
Craig Howell

Reputation: 1194

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;

Figured it out!!! Here it is in case anyone comes across this bonus question:

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 j.%I::text FROM %I as t INNER JOIN %I as j ON t.%I::text = j.%I::text ORDER BY j.%I::text ASC;',
        join_column_name, table_name, join_table_name, table_column_name, join_table_column_name, join_column_name
      );
  END;
$BODY$
LANGUAGE plpgsql;

Upvotes: 4

Views: 2887

Answers (1)

Mansueli
Mansueli

Reputation: 6984

You can create dynamic RPCS with postgres, but if the types of columns are variable, then you'll probably need to convert them to text (::text):

CREATE OR REPLACE FUNCTION get_distict(tname text, cname text)
RETURNS TABLE ( distinct_values text) AS 
$BODY$
  BEGIN
      RETURN QUERY EXECUTE format(
        'SELECT %I::text FROM %I GROUP BY %I ORDER BY %I ASC;',
                                       cname,   tname,    cname, came
      );
  END;
$BODY$
LANGUAGE plpgsql;

Then, you can call this dynamic function like:

select get_distict('customers','owner');

Upvotes: 4

Related Questions