Supabase•3y ago

RPC function not working but no error is returned.

So I have my RPC function in supabase Database Functions. It was working at the first time but now for some reason it stopped working. My function have row_id integer argument:

  update images
  set likes = likes + 1
  where id = row_id;

  update images
  set likes = likes + 1
  where id = row_id;

and this is my js to run it:

      
  await supabase.rpc('increment', {row_id: id}).then(async (res) => 
  {
    //do stuff...
  })

      
  await supabase.rpc('increment', {row_id: id}).then(async (res) => 
  {
    //do stuff...
  })

Response of this rpc have no error or any specific data. Any ideas?

32 Replies

jdgamble555•3y ago

Can you share you whole function?

floyareOP•3y ago

I mean rpc call is already written, and .then function content is not important because further actions are not doing anything with images table. It's just the problem with this supabase.rpc function.

jdgamble555•3y ago

We need to view the whole function to make your it is being called correctly. Go go Database, click "Database Functions," click the menu next to it, and click "edit function" to get the full function code.

floyareOP•3y ago

Well, this:

  
update images
set likes = likes + 1
where id = row_id;

  
update images
set likes = likes + 1
where id = row_id;

is entire code and It was working but not now.

jdgamble555•3y ago

no, with the input and everything

floyareOP•3y ago

but that's everything I have here

floyareOP•3y ago

jdgamble555•3y ago

You're not inputting anything, that is the problem. Delete your function, go to sql and type something like:

create function increment (row_id int)
return void as
$$
  update images
  set likes = likes + 1
  where id = row_id;
$$
language sql volatile;

create function increment (row_id int)
return void as
$$
  update images
  set likes = likes + 1
  where id = row_id;
$$
language sql volatile;

See if that works...

floyareOP•3y ago

it gives an error Failed to validate sql query: syntax error at or near "return"

jdgamble555•3y ago

Try:

CREATE OR REPLACE FUNCTION increment (row_id int)
RETURNS void AS
$$
BEGIN
  update images
  set likes = likes + 1
  where id = row_id;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION increment (row_id int)
RETURNS void AS
$$
BEGIN
  update images
  set likes = likes + 1
  where id = row_id;
END;
$$
LANGUAGE plpgsql;

floyareOP•3y ago

same thing

jdgamble555•3y ago

ah... use RETURNS with an s

floyareOP•3y ago

well function now looks like this

BEGIN
  update images
  set likes = likes + 1
  where id = row_id;
END;

BEGIN
  update images
  set likes = likes + 1
  where id = row_id;
END;

and it's still not working

jdgamble555•3y ago

Run the above code in SQL, you need the WHOLE code, not just the middle part

floyareOP•3y ago

I just done it It created this database function and it looks like this code above.

jdgamble555•3y ago

you ran it with RETURNS spelled correctly? Do you get an error?

floyareOP•3y ago

yes no errors in sql but it's still not working

jdgamble555•3y ago

How is it not working?

floyareOP•3y ago

that's good question i looked up in api logs and postgres logs and saw no error either

jdgamble555•3y ago

If you run this straight in sql:

update images
set likes = likes + 1
where id = row_id;

update images
set likes = likes + 1
where id = row_id;

Does it work as expected?

floyareOP•3y ago

update images
set likes = likes + 1
where id = 2;

update images
set likes = likes + 1
where id = 2;

yes it's working in sql

jdgamble555•3y ago

You could also do,

UPDATE images
SET likes += 1
WHERE ID = row_id;

UPDATE images
SET likes += 1
WHERE ID = row_id;

fwi probably not the issue

floyareOP•3y ago

Failed to create function: failed to update pg.functions with the given ID: syntax error at or near "+="

Failed to create function: failed to update pg.functions with the given ID: syntax error at or near "+="

jdgamble555•3y ago

ignore that, different sql

CREATE OR REPLACE FUNCTION increment(row_id int)
RETURNS void AS
$$
BEGIN
  UPDATE images
  SET likes = likes + 1
  WHERE id = row_id;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION increment(row_id int)
RETURNS void AS
$$
BEGIN
  UPDATE images
  SET likes = likes + 1
  WHERE id = row_id;
END;
$$
LANGUAGE plpgsql;

This should work, not sure why it doesn't. Not sure if it could be RLS or if you don't have the plpgsql extension installed?

floyareOP•3y ago

nah still not working oh wow ok the issue is rls i just disabled it and now it's working

jdgamble555•3y ago

Yeah, I'm trying to figure out why you need it in functions...

floyareOP•3y ago

but that's weird because normally I can UPDATE and INSERT without problems because I made rls policies for inserting and updating only for authenticated users. but rpc is having problem with it

jdgamble555•3y ago

Perhaps @garyaustin can answer why functions use RLS, which is odd to me...

garyaustin•3y ago

RLS applies to “security invoker” (default) functions. The tables they use must be accessible to the user making the call. So would work in SQL editor but not the API is anon or signed user can’t do the table operations. “Security definer” functions run as the user who created the function so usually bypass RLS.

jdgamble555•3y ago

So how would you write a function to get around the security access?

garyaustin•3y ago

Add "security definer" to its definition .

$$  LANGUAGE plpgsql
    SECURITY DEFINER

$$  LANGUAGE plpgsql
    SECURITY DEFINER

floyareOP•3y ago

It works now! Ty

Gaming

Programming

RPC function not working but no error is returned.

Did you find this page helpful?