https://supabase.com/ logo
Join Discord
Powered by
Hi, I'm having trouble getting an SQL function to ...
# sql
j
Hi, I'm having trouble getting an SQL function to work with RPC, even though I was able to get a very similar function to work 🙂 I'm able to run this query successfully to create the function:
Copy code
create or replace function add_rating_count_artist (artistid integer) 
returns void as
$$
  update artists
  set ratings = ratings + 1
  where id = artistid;
$$ 
language sql volatile;
Then in my app I call:
Copy code
async function addRatingCountToArtist(artistId) {
    const { data, error } = await supabase.rpc( 'add_rating_count_artist', { artistid: artistId })
  }
When I check the db, the 
ratings
value has not changed. FYI, the 
artistId
I'm passing in is a number. table name is 
artists
, 
id
is an 
int4
, 
ratings
is an 
int4
. Any ideas? Thank you
s
If you 
console.log({ error })
, it should give you some more insight into what the problem was
j
Thank you, I'm looking into postgres docs now, I think I need to change the return value to something other than void to see the error. Do you know if that's the case? My actual function is
Copy code
async function addRatingCountToArtist(artistId) {
    console.log('in add rating count to artist', artistId)
    const { data, error } = await supabase.rpc( 'add_rating_count_artist', { artistid: artistId })
    if (error) {
      console.log('error adding rating count to artist', error)
    } if (data) {
      console.log('data from adding rating count to artist', data)
    }
  }
the first console log is firing, but the data or error ones aren't
s
Can you try running that function from the sql editor in the Supabase UI and see if its working
j
Success. No rows returned
Nevermind, that's for creating or replacing it. one moment
syntax error at or near "add_rating_count_artist"
(when I run 
add_rating_count_artist(1)
in the sql editor.
(1 is a valid value)
s
I hate those error messages so much, they don't tell you much
j
judging by this video I think I passed the 
1
argument correctly

https://youtu.be/MJZCCpCYEqk?t=645â–¾

.
Any ideas how to proceed? I'm going to create a brand new table and new function and see if that helps, because I have been adding and changing columns in the 
artists
table
s
Let me try and get my playground project resumed and I will try something out
j
Also, FWIW, running my working function in the sql editor returns the same type of error. 
add_one_point('Henrietta')
return 
syntax error at or near "add_one_point"
, function is
Copy code
create or replace function add_one_point (username text) 
returns void as
$$
  update profiles
  set points = points + 1
  where name = username;
$$ 
language sql volatile;
thank you @User !
s
Ok the function seems to work
You have to run it inside the SQL editor with a 
select add_rating_count_artist(1)
j
Yes!!! beautiful
So, I guess the question is why it isn't firing/working when (attempting to ) call with RPC
s
Can you check that your artistId is actually a integer and not a string when passed in
Copy code
js
const { data, error } = await supabase.rpc( 'add_rating_count_artist', { artistid: parseInt(artistId) })
j
Still not adding, though it did add one when I ran it manually (in sql editor). I added 
parseInt
, i also added 
typeof
to the console log it's a number
s
Let me test it out from a rpc in my project
j
FYI I tried 
const { data, error } = await supabase.rpc( 'add_rating_count_artist', { artistid: 1 })
, didn't work 🙂
s
ok found the issue
RLS is enabled on that table
You would either need to create the function to bypass RLS or make sure the user is logged in and conforms to the RLS rule for that table before trying to call that 
.rpc
j
Awesome thanks., I'll give it a go
s
To create a function to bypass RLS just add 
SECURITY DEFINER
after volatile
Ok time to destroy my playground
j
Thanks a ton!
I had looked at RLS but didn't think that was the issue, thanks again. Working now!
PreviousNext
Powered by