https://supabase.com/ logo
Powered by
#help
Title
# help
e

elhe26

03/25/2022, 2:11 PM
Hi all. I'm using rpc to insert values to a table. After I execute the function, I'm getting this error: 
{"code":"42P01","hint":null,"details":null}
n

Needle

03/25/2022, 2:11 PM
Hello ! This thread has been automatically created from your message in a ``few seconds ago``. Pinging @User so that they see this as well! 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.
e

elhe26

03/25/2022, 2:13 PM
Using the dashboard, I created a function using the following configuration: return type: record Arguments email - text password - text acctkey - text updatedat int8 Definition
Copy code
begin
insert into public.Users(email, password, acctKey, updatedAt)
values (email, crypt(password, gen_salt('bf')), acctkey, updatedat) returning id, password;
end;
Advanced Settings Language: * plpgsql* Type of security: Security Definer
Calling the function:
Copy code
let {data, error } = await supabaseClient.rpc('function_one', {
'email': 'john@example.com',
'password': 'mypass',
'acctkey':'key1',
'updatedat': Date.now()
});
I enabled 
PLPGSQL
, 
AUTOINC
, and 
PGCRYPTO
on Extensions page.
s

Scott P

03/25/2022, 2:20 PM
I wish we had more help requests with this much info 🙂 As for the problem, error 42P01 denotes the database query is on an undefined table. I notice that your table starts with an uppercase letter. You have 2 options: - Rename the table from 
Users
to 
users
- Quote the table like 
insert into "public.Users"
(it may also be 
public."Users"
- I'm not sure as I've only ever dealt with snake_case table names) The first option can prevent a lot of easily avoidable issues, while the second option is quicker.
n

Needle

03/25/2022, 2:20 PM
elhe26 (2022-03-25)
e

elhe26

03/25/2022, 2:21 PM
Interesting.
insert into public."users"
or 
insert into public.users
?
s

Scott P

03/25/2022, 2:24 PM
If you rename the table so it's all lowercase, you don't need the quotes. If you're not renaming the table, then it's either 
insert into public."Users"
, or 
insert into "public.Users"
, but I'm not sure which is correct
e

elhe26

03/25/2022, 2:28 PM
Will this matter when using client sdks? ie.
supabaseClient.from("Users").upsert
Thanks, hehe. Adding all the info would help you troubleshooting faster which will help me with a prompt answer. Win-Win.
Trial & Error. I changed the definition to 
insert into public."Users"
and I got this error: 
{"code":"42703","hint":null,"details":null}
s

Scott P

03/25/2022, 2:36 PM
42703 = column does not exist. Make sure you also quote any columns which aren't all lowercase (e.g. 
"acctKey"
and 
"updatedAt"
)
e

elhe26

03/25/2022, 2:36 PM
Using 
insert into "public.Users"
: 
{"code":"42P01","details":null,"hint":null}
So basically 
public."Users"
is the right way if we're using uppercase names.
Let me try it. I used 
supabaseClient.from("Users").upsert
with camelcase variables (ie. 
updatedAt
) and I didn't have any issues. It seems the same cannot be said when defining RPCs.
Now I got this: 
{"hint":null,"code":"42601","details":null}
s

Scott P

03/25/2022, 2:45 PM
Yeah, RPC functions are run directly inside Postgres, and postgres is strict about what it accepts in terms of names (when run via functions or as queries directly run on the DB). The 
.from()
call is run via the PostgREST endpoint, so it's capable of doing some clever mapping based upon the database structure. It's also likely that it quotes the table name to avoid issues.
Try running the query directly with some dummy data and see what response it provides. It should give you some idea on what the problem is.
e

elhe26

03/25/2022, 2:46 PM
So, I have the following: ``begin insert into public."Users"("email", "password", "acctKey", "updatedAt") values (email, crypt(password, gen_salt('bf')), acctkey, updatedat) returning id, "password"; end;`
Ok. Let me try it.
Is there a way to run the RPC within the dashboard?
s

Scott P

03/25/2022, 2:52 PM
SELECT * FROM function_name(arg1, arg2, arg3)
The query you listed just above your latest message, try running that directly in the SQL console in the dashboard without 
begin
or 
end;
, and add some dummy data for 
email
password
, 
acctkey
and 
updatedat
in the 
values
part of the query.
e

elhe26

03/25/2022, 3:00 PM
Interesting. It says the column "updatedAt" doesn't exist.
I got another rpc function for validation and I'm getting the same result. This is the definition:
Copy code
begin
return (select * from public."Keys" where "keyName" = keyname AND "keyLoc" = keyloc  limit 1);
end;
I'm getting 
column "myKeyName1" does not exist
Any suggestions?
I'm getting the same error. It seems that the where clause is assuming 
"myKeyName1"
value is the actual column.
I'm testing it using this function.
s

Scott P

03/25/2022, 3:25 PM
Honestly, the best suggestion I have would be to rename the tables and columns to be all lowercase, or snake_case, because it seems like you're going to keep having these sorts of errors
e

elhe26

03/25/2022, 3:27 PM
that's a bummer, hehe.
By doing it I won't need to wrap any column in "", right?
s

Scott P

03/25/2022, 3:30 PM
Correct. It's a pain to have to rename everything, but it avoids a lot of issues and workarounds
e

elhe26

03/25/2022, 6:24 PM
Thanks Scott. Everything is working now.
Is there a way to add documentation on how to implement everything (postgres functions, rpc using sdk clients, etc...) step by step?
Powered by