Hello 👋 I'm building an RPC and accessing it from a JS (SvelteKit). I'm trying to build the call from JS, but am getting errors and am unsure what exactly is causing it.

Hello @monsto! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User role so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ... menu) and select Leave Thread to unsubscribe from future updates. Want to change the title? Use the /title command! We have solved your problem? Click the button below to archive it.

js function

; ({ data } = await supabase
                .rpc("gen_report_epr", {
                    daterange_start: dateFrom.toISOString(),
                    daterange_end: dateTo.toISOString(),
                })
            )

rpc sql

create temporary table temp_ouput_epr as (select * from electronic_purchase_report) with no data;

    create or replace function gen_report_epr(daterange_start date, daterange_end date)
    returns setof temp_ouput_epr
    as $BODY$
      select * from electronic_purchase_report where created_at between daterange_start and daterange_end
    $BODY$ language sql;

https://postgrest.org/en/stable/api.html#custom-queries probably the best document, although it is in the url/body format, it shows postgres functions and the format of the parameters the .rpc(func,parms) passes in the body.

does my code look ok tho?

What is your error? The call and parameter format look correct.

I'm seeing just now that I'm getting a 404

If you goto database gui and functions section you should see the function

I don't.

It still should show up. What do you get when you click run

run returns zero, which i get because no parameters.

So there is a first step that creates the function with sql and you use run to do that. Nothing to do with running the actual function

tried to paste the msg here but windows is being stupid.

Should return "Success. No rows returned"

"success. no rows returned."

Not sure why not showing up....

did you do anything more than than just put in the query and it magically appeared in the functions list?

that is it

well shit.

that is your rpc call coming thru the api.

right so it's getting that far.

Yeah but 404 means it has an issue, l think that the function name with matching parameters was not found... but not sure what else it could mean.

just to rule out other chars, I c/p the name gen_report_epr

I just removed public. and it still gets created in the public schema which is needed

wait your return type is different.

That should not matter on finding the function

it's why I used the query instead of the gui, because i didn't think any of the return types in the gui would give the 35k rows that need to come back.

You are correct on the create gui, I wonder if even the display does not show.

(being alpha feature and all)

run your sql again without the or replace you should get an error

"without the" what 😁

create or replace function gen_report_epr(daterange_start drop "or replace"

success . . .

that is not good...

success multiple times.

I've never done the temp table approach, versus table return

I couldn't get the syntax to return the table.

I don't know the syntax for your approach, it is acting though like the create table is not running

original (for reference)

create temporary table temp_ouput_epr as (select * from electronic_purchase_report) with no data;

create function gen_report_epr(daterange_start date, daterange_end date)
returns setof temp_ouput_epr
as $BODY$
  select * from electronic_purchase_report where created_at between daterange_start and daterange_end
$BODY$ language sql;

can't you do "returns setof electronic_purchase_report"

!! success

so your front sql syntax was causing the last part not to be run... odd

yeah the temp table part? It appears so.

@monsto you mentioned 35000 records. The api limit setting (default 1000) in API settings does apply to rpc record returns also.

yeah we've been wrestling with that. Should I start a new thread?

Probably. But you have 3 choices with rpc, paginate it with range just like a regular select, up the limit or return one large json object.

we have it set to 50k in api settings.

if they are small records I would think that would be an option.

the chunk query:

; ({ data, error, count } = await supabase
                    .from('electronic_purchase_report')
                    .select('*', { count: 'exact' })
                    .gte('created_at', dateFrom.toISOString())
                    .lte('created_at', dateTo.toISOString())
                    .range(from, to)
                    .then(e => e)
                    .catch(error => console.log(`conlog: supabase catch`, error))
                )

If you already have pagination method, I'd just stick with that. I don't have how to do it at my fingers, but you should be able to return from the postgres function a single json object and handle that in rpc.

I'm not sure how the data comes back. I thought it already was json.

postgrest is turning the records to json

oh ok so have the rpc turn it to json, then return that

it also sets the limit on records, if your function provides one json response it would return that.