RPC function issue with UPDATE Supabase #help

RPC function issue with UPDATE

STILLWATER;

07/19/2022, 1:35 PM

RPC function issue with UPDATE

Needle

07/19/2022, 1:35 PM

Hello @STILLWATER;! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User 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.

STILLWATER;

07/19/2022, 1:35 PM

Hey So i was creating this function to update value using rpc in supabase

STILLWATER;

07/19/2022, 1:36 PM

Copy code

sql
create function update_story_count(uid uuid, change_value int)
    returns void
    language plpgsql
as
$$
BEGIN
         (
            UPDATE "Users" U
              SET stories_count = stories_count + change_value
              WHERE U.id=uid
            );
END
$$;

STILLWATER;

07/19/2022, 1:36 PM

But it says

STILLWATER;

07/19/2022, 1:37 PM

all of my other function are like this, just it starts with

Copy code

return query (...)

STILLWATER;

07/19/2022, 1:37 PM

but that was causing error as the function was not returning anything

STILLWATER;

07/19/2022, 1:37 PM

so i removed return query

STILLWATER;

07/19/2022, 1:37 PM

https://github.com/supabase/supabase/discussions/909

STILLWATER;

07/19/2022, 1:37 PM

i was referencing this

STILLWATER;

07/19/2022, 1:37 PM

any help?

STILLWATER;

07/19/2022, 1:51 PM

tried removing brackets etc every permutation possible

STILLWATER;

07/19/2022, 1:51 PM

only change is on the discussion they using sql volatile but mine is plpgsql is that an issue?

STILLWATER;

07/19/2022, 1:51 PM

idk whats sql volatile so didnt changed that

silentworks

07/19/2022, 2:23 PM

You can just write it as

sql stable

there is no need for

plpsql

since you aren't using any of its features in this query. But your existing code should work too by just removing the brackets. I tested this and it worked for me.

Copy code

sql
create function update_story_count(uid uuid, change_value int)
    returns void
    language plpgsql
as
$$
BEGIN
    UPDATE "Users" U
    SET stories_count = stories_count + change_value
    WHERE U.id = uid;
END
$$;

STILLWATER;

07/19/2022, 2:23 PM

HOLY HOW!

STILLWATER;

07/19/2022, 2:28 PM

also

silentworks

07/19/2022, 2:28 PM

You only get 1 question per go.

STILLWATER;

07/19/2022, 2:28 PM

got it thanks

silentworks

07/19/2022, 2:28 PM

I'm joking 🤣

STILLWATER;

07/19/2022, 2:28 PM

bruh

silentworks

07/19/2022, 2:28 PM

Hahahaha

STILLWATER;

07/19/2022, 2:28 PM

🔪

STILLWATER;

07/19/2022, 2:29 PM

can i select the updated data from same query

silentworks

07/19/2022, 2:29 PM

Sorry I thought you would have caught on to that

STILLWATER;

07/19/2022, 2:29 PM

i checked online solutions

silentworks

07/19/2022, 2:29 PM

Yeah you can

STILLWATER;

07/19/2022, 2:29 PM

those online solution didnt work

silentworks

07/19/2022, 2:29 PM

So you would need to change the return type and then run a select query after

silentworks

07/19/2022, 2:30 PM

Let me test out a solution and then get back to you on here

STILLWATER;

07/19/2022, 2:30 PM

thanks a lot

silentworks

07/19/2022, 2:58 PM

I haven't tested this with the

.rpc

call but it works inside the SQL editor

Copy code

sql
create or replace function update_story_count(uid uuid, change_value int)
    returns TABLE (id uuid, stories_count int)
as
$$
    UPDATE "Users" U
    SET stories_count = stories_count + change_value
    WHERE U.id = uid
    RETURNING U.id, U.stories_count; -- tells it which data to return
$$
language sql; -- changed to sql instead of plpsql

STILLWATER;

07/19/2022, 3:02 PM

does it not work on plpgsql?

silentworks

07/19/2022, 3:03 PM

It's not using any

plpgsql

specific feature in here

STILLWATER;

07/19/2022, 3:03 PM

just for uniformity 😅

STILLWATER;

07/19/2022, 3:17 PM

"success": false, "log": "column reference \"stories_count\" is ambiguous"

STILLWATER;

07/19/2022, 3:17 PM

:((

STILLWATER;

07/19/2022, 3:17 PM

ambiguity in SET

silentworks

07/19/2022, 3:18 PM

You changed it to

plpgsql

right?

STILLWATER;

07/19/2022, 3:18 PM

yeah

silentworks

07/19/2022, 3:18 PM

haha

STILLWATER;

07/19/2022, 3:18 PM

ill change to sql?

STILLWATER;

07/19/2022, 3:18 PM

damn it makes a diff?!?

silentworks

07/19/2022, 3:18 PM

Indeed it does

STILLWATER;

07/19/2022, 3:20 PM

damn bro ur a lifesaver

STILLWATER;

07/19/2022, 3:20 PM

thanks!

STILLWATER;

07/19/2022, 3:20 PM

also why BEGIN END doesnt work here?

STILLWATER;

07/19/2022, 3:21 PM

is that syntax for plpgsql specifically?

silentworks

07/19/2022, 3:57 PM

Yes, you can read more about it here https://www.postgresqltutorial.com/postgresql-plpgsql/plpgsql-block-structure/

STILLWATER;

07/19/2022, 3:58 PM

thanks

Previous Next