RPC client does not receive data when function contains joined tables

[stsmedia]

I have created a RPC function in supabase like so:

create or replace function test(input timestamp)
  returns table ("res_time" timestamp, "res_symbol" text, "res_price" float8) 
as
$$
SELECT m.time as res_time, m.symbol as res_symbol, m.price as res_price
FROM market_data m
INNER JOIN (
    SELECT symbol, MIN(market_data.time) as time
    FROM market_data
    WHERE time > input
    GROUP BY symbol
) i ON m.symbol = i.symbol and m.time = i.time;
$$
language sql
stable;

Running this function in the SB code editor gives me the results I want:

select * from test('2024-03-29 09:47:06');

Running the same function from flutter gives me a list with no results:

final test = await Supabase.instance.client.rpc('test', params: {'input': '2024-03-29 09:47:06'});

I have tried to change the returns definition in the function to use

returns setof market_data

with the same outcome (0 rows returned to the flutter client but full results for the query in the SB SQL terminal).

Removing the inner join entirely from the function does work (rows do get returned to the flutter client).

Is this a known issue with a workaround or a straight bug?

[dshukertjr]

Hi there,

The cause most likely is due to your RLS policy settings. You have created a security definer function (the default for creating a function). That means that the caller of the function has to have the proper permission defined by the RLS policies to read the underlying tables.

Disable the RLS for all the tables within your function and see if you get data back on your Flutter app. If you do, then make adjustments to your RLS policy so that the caller of the function has permission to read the data.

[stsmedia]

Good catch, this was the only table where I had RLS enabled and did not check. My bad. Thanks for pointing it out.