Topics

Supabase•3y ago

Syntax error in postgress functions

Hi there, I'm very new to this whole database thing and thought that supabase seemed not so intimidating. I am however running into a problem. I'm following this tutorial https://www.youtube.com/watch?v=I6nnp9AINJk because just like he shows in the video I want to increment a value. I'm pretty sure I've followed the guide to a tee, yet supabase is giving me a syntax error and it's really not that helpful because it's just saying at or around variable name. Maybe one of the more advanced users could point out where the problem occurs 🙏

declare
  new_amount int;
begin 
  select amount 
  into new_amount
  from public.collection
  where id = card_id

  new_amount = new_amount + 1;

  update public.collection
  set amount = new_amount
  where id = card_id;

  return new_amount;
end

declare
  new_amount int;
begin 
  select amount 
  into new_amount
  from public.collection
  where id = card_id

  new_amount = new_amount + 1;

  update public.collection
  set amount = new_amount
  where id = card_id;

  return new_amount;
end

I've set the argument to be card_id. The specific error is: Failed to create function: failed to create pg.functions: syntax error at or near "new_amount"

YouTube

Call Postgres functions from JavaScript with RPC

For the introductory video on PostgreSQL functions, check out 👉 https://www.youtube.com/watch?v=MJZCCpCYEqk Sometimes we have a collection of complex steps we need to perform in a particular sequence from the front-end. Maybe we want to execute an SQL statement that the supabase-js library does not yet support. These are both excellent use case...

40 Replies

thestepafter•3y ago

You are missing a closing ; @JuicyBenjamin. I recommend using a database tool instead of the dashboard UI if you are just learning SQL. I personally use and love Datagrip.

JuicyBenjaminOP•3y ago

Is it just the semi colon? and where do I put it?

thestepafter•3y ago

No description

thestepafter•3y ago

Not sure if it is just the semi-colon, please add and confirm.

garyaustin•3y ago

You need a semi colon after card_id to finish the statement at least.

thestepafter•3y ago

Your end needs a semi colon too. And I don't see that you are using $$ anywhere?

garyaustin•3y ago

The function UI does not need $$.

thestepafter•3y ago

So you may run into escaping issues unless the dashboard doesn't require that. Ah, ok.

JuicyBenjaminOP•3y ago

Well, now I'm at least getting a new error: Failed to create function: failed to create pg.functions: syntax error at or near "return"

thestepafter•3y ago

Put a semi colon after end as well.

JuicyBenjaminOP•3y ago

Yea I did, same result why is it semi colon some places but not all places? xD

thestepafter•3y ago

Semi colon is wherever the code block ends.

thestepafter•3y ago

https://www.postgresql.org/docs/14/sql-syntax.html this is a quick read and will probably help a lot.

PostgreSQL Documentation

Chapter 4. SQL Syntax

Chapter 4. SQL Syntax Table of Contents 4.1. Lexical Structure 4.1.1. Identifiers and Key Words 4.1.2. Constants 4.1.3. Operators 4.1.4. Special Characters …

thestepafter•3y ago

I'm rebuilding the function in one of my databases and then I can let you know what is missing, just give me a minute.

create function public.test() returns boolean
    language plpgsql
as
$$
declare
  new_amount int;
begin
  select amount
  into new_amount
  from public.collection
  where id = card_id;

  new_amount = new_amount + 1;

  update public.collection
  set amount = new_amount
  where id = card_id;

  return new_amount;
end;
$$;

create function public.test() returns boolean
    language plpgsql
as
$$
declare
  new_amount int;
begin
  select amount
  into new_amount
  from public.collection
  where id = card_id;

  new_amount = new_amount + 1;

  update public.collection
  set amount = new_amount
  where id = card_id;

  return new_amount;
end;
$$;

This creates in the database manually with no issues. All I did was add the semi colons. What are you doing that throws this error? Failed to create function: failed to create pg.functions: syntax error at or near "return"

garyaustin•3y ago

returns should be int though...

thestepafter•3y ago

Oh, you are correct. That was my bad, I cut and paste some other code at the top.

JuicyBenjaminOP•3y ago

dunno, that's all the code I have :/

thestepafter•3y ago

What are you clicking or doing to see that error? I'm trying to replicate on my side.

JuicyBenjaminOP•3y ago

I'm just trying to save the function But it wont let me

garyaustin•3y ago

Can you show your code again? Also don't think it would error on save but did you declare a return value and confirm you are using thefunction UI versus sql window.

thestepafter•3y ago

Good point, are you using "Add New Function" or the SQL query window? This is the Add New Function.

thestepafter•3y ago

No description

JuicyBenjaminOP•3y ago

create a new function, under database --> functions in supabase yea that one

garyaustin•3y ago

where it says void in the pix, do you have int?

thestepafter•3y ago

I just created a new function fine in the UI for Create New Function.

JuicyBenjaminOP•3y ago

no, should it be int?

garyaustin•3y ago

Yes, so it may be checking on save.

thestepafter•3y ago

No description

thestepafter•3y ago

  new_amount int;
begin
  select amount
  into new_amount
  from public.collection
  where id = card_id;

  new_amount = new_amount + 1;

  update public.collection
  set amount = new_amount
  where id = card_id;

  return new_amount;
end;

  new_amount int;
begin
  select amount
  into new_amount
  from public.collection
  where id = card_id;

  new_amount = new_amount + 1;

  update public.collection
  set amount = new_amount
  where id = card_id;

  return new_amount;
end;

That works for me with no errors.

JuicyBenjaminOP•3y ago

which of the int types, just int 8 or?

thestepafter•3y ago

Depends on the estimated size of the int. I just did 8 for testing purposes. If you are using low dollar amounts, could probably go lower but to be safe, int8 should be fine. You can also just do integer.

JuicyBenjaminOP•3y ago

hmm, still giving me error at or near return

thestepafter•3y ago

(Which is what I should have done but didn't see it immediately.) You cut and paste the above code into the code box?

JuicyBenjaminOP•3y ago

I can try, should it not have the "declare" ?

thestepafter•3y ago

I just tested with declare and it works as well.

  new_amount int;
begin
  select amount
  into new_amount
  from public.collection
  where id = card_id;

  new_amount = new_amount + 1;

  update public.collection
  set amount = new_amount
  where id = card_id;

  return new_amount;
end;

  new_amount int;
begin
  select amount
  into new_amount
  from public.collection
  where id = card_id;

  new_amount = new_amount + 1;

  update public.collection
  set amount = new_amount
  where id = card_id;

  return new_amount;
end;

Wait, Discord is removing my first line of code. declare new_amount int; begin select amount into new_amount from public.collection where id = card_id; new_amount = new_amount + 1; update public.collection set amount = new_amount where id = card_id; return new_amount; end;

declare
  new_amount int;
begin
  select amount
  into new_amount
  from public.collection
  where id = card_id;

  new_amount = new_amount + 1;

  update public.collection
  set amount = new_amount
  where id = card_id;

  return new_amount;
end;

declare
  new_amount int;
begin
  select amount
  into new_amount
  from public.collection
  where id = card_id;

  new_amount = new_amount + 1;

  update public.collection
  set amount = new_amount
  where id = card_id;

  return new_amount;
end;

Sorry about that mess, my code isn't pasting properly for some reason. That last block is what I used and it works fine. Can you send a screenshot of the function window please?

JuicyBenjaminOP•3y ago

seems to work now, but it did change something. Fingers crossed the function will work as intended Thanks for the help guys

thestepafter•3y ago

Ah, ok, great! Supabase is just a UI and a bunch of PostgreSQL features pre-installed. I highly recommend using a database tool such as Datagrip and learning pl/pgsql structures, that will make everything so much easier to work with. (Supabase is fantastic, don't get me wrong, but working directly in the database for most of your work will be much easier than using the web UI.)

JuicyBenjaminOP•3y ago

I'll look into it, thanks

garyaustin•3y ago

I 2nd datagrip, but it is an free addon for my ide, not sure what it costs. There are other options out there too.

thestepafter•3y ago

Datagrip is $10 / month. There are also free tools like DBeaver. TablePlus is really nice.

Supabase gives you the tools, documentation, and community that makes managing databases, authentication, and backend infrastructure a lot less overwhelming.

40KMembers

View on Discord

Did you find this page helpful?