Dynamic columns inside RPC function

I need to do a simple query: SELECT [columns_list], SUM(column) FROM table GROUP BY [columns_list]

Of course GROUP BY is not available using Supabase JS Client.
So, I'm trying to write an RPC function, but I'm struggling with allowing a variable columns list as an argument of the function.

CREATE OR REPLACE FUNCTION test (groupby text)
    RETURNS SETOF RECORD
    AS $$
BEGIN
    RETURN query EXECUTE format('select %I, sum(count) as c from test_table group by %I', groupby, groupby);
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test (groupby text)
    RETURNS SETOF RECORD
    AS $$
BEGIN
    RETURN query EXECUTE format('select %I, sum(count) as c from test_table group by %I', groupby, groupby);
END;
$$
LANGUAGE plpgsql;

Something like this works, but allows only one column at a time, and probably is something that I can't use with Supabase JS client, because the query is something like this:

select * from test('column_name') f(column_name uuid, count bigint)

select * from test('column_name') f(column_name uuid, count bigint)

Is there any solution that I'm missing? I can create a NextJS API with a custom Prisma query, but I prefer a solution with an RPC call, if there's one.

Supabase•4y ago

Angelo

Dynamic columns inside RPC function

CREATE OR REPLACE FUNCTION test (groupby text)
    RETURNS SETOF RECORD
    AS $$
BEGIN
    RETURN query EXECUTE format('select %I, sum(count) as c from test_table group by %I', groupby, groupby);
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test (groupby text)
    RETURNS SETOF RECORD
    AS $$
BEGIN
    RETURN query EXECUTE format('select %I, sum(count) as c from test_table group by %I', groupby, groupby);
END;
$$
LANGUAGE plpgsql;

Something like this works, but allows only one column at a time, and probably is something that I can't use with Supabase JS client, because the query is something like this:

select * from test('column_name') f(column_name uuid, count bigint)

select * from test('column_name') f(column_name uuid, count bigint)

Is there any solution that I'm missing? I can create a NextJS API with a custom Prisma query, but I prefer a solution with an RPC call, if there's one.

Dynamic columns inside RPC function

Similar Threads

Dynamic columns inside RPC function

Similar Threads

Similar Threads

Similar Threads