count for rpc that returns a table?

[city41]

I have a postgres function that returns a table

CREATE OR REPLACE FUNCTION "get_newest_published_levels"(current_user_id varchar(50))
  RETURNS table (id text, name text, data jsonb, version text, username text, created_at timestamp, updated_at timestamp, total_vote_count bigint, current_user_voted bool)
  AS $pga$
    begin
      return query
      select 
        l.id,
        l.name,
        l.data,
        l.version,
        u.username,
        l.created_at,
        l.updated_at,
        coalesce(count(lv.user_id), 0) as total_vote_count,
        coalesce(count(case lv.user_id::text when current_user_id then 1 else null end), 0) = 1 as current_user_voted
      from
        public.levels
   ...

(truncated for brevity)

And I call it like this

const { data, error } = client.rpc('get_newest_published_levels', params)
    .range(0, 10);

But I'd also like to get the total count of all rows, similar to a standard select

const { data, error } = client.rpc('get_newest_published_levels', params)
    .select('*', { count: 'exact' })
    .range(0, 10);

But it seems like count only exists on SupabaseQueryBuilder and not PostgresTransformBuilder.

Am I missing something, or is this a missing feature? Since the function returns a table, is it possible to port count to rpc? Or is there a limitation preventing this?

[steve-chavez]

Just to note that rpc does support an exact: count at the PostgREST side. This should be raised as a feature request on postgrest-js

[steve-chavez]

But there is no sign of the count in the response anywhere that I can find.

Did you check the Content-Range header? The count should be at Content-Range: *-*/<count>.

[city41]

Doh, I was wrong. It did log null for count earlier, but just now when trying it, it is logging the count out just fine. And yeah it does appear to be coming back on the Content-Range header.

So basically const { count } = await client.rpc('some_function', params, { count: 'exact' }) does work.

Thanks for the help!

[tatiana1337]

Doh, I was wrong. It did log null for count earlier, but just now when trying it, it is logging the count out just fine. And yeah it does appear to be coming back on the Content-Range header.

So basically const { count } = await client.rpc('some_function', params, { count: 'exact' }) does work.

Thanks for the help!

I'm sorry, but how did you query a table from rpc? I'm just getting a single element, not table :\

[city41]

You have to have the function specify it returns a table

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

[emilianoyaryura]

when doing

        const response = await supabase.rpc('get_posts_by_following', { following_ids: following, start_range: start, end_range: end - 1 }, {
           count: "exact",
        });

count is the same as the requested range (29), when in fact, there are thousands of posts (which should be the real count)

someone knows how to fix this?