https://supabase.com/ logo
Join DiscordCommunities
Powered by
PostGres Function to update table.
# help-and-questions
x
So I want to update a column ("quantity") in the table grocerystores anytime there is an insert, update ,or delete on the grocerystoreitems. Right now the first query does successfully return the SUM of the quantity field in the grocerystoreitems. However when I run this I get an error "Invalid SQL query" error. Right now I just want to make sure the function works by calling it in the SQL editor and then I am going to set up a trigger on it.
Copy code
sql
CREATE OR REPLACE FUNCTION update_quantity(storeId bigint)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  BEGIN;
    SELECT SUM(quantity) AS total_quantity
    INTO total_quantity
    FROM public.grocerystoreitems 
    WHERE "storeId" = storeId AND quantity > 0;

    UPDATE grocerystores
    SET quantity = total_quantity
    WHERE Id = storeId;

    COMMIT;
  END;
$$;

https://cdn.discordapp.com/attachments/1111459092783968317/1111459093224378388/Screenshot_2023-05-25_at_7.58.09_PM.png▾

u
How about this?
Copy code
sql
CREATE OR REPLACE FUNCTION update_quantity(storeId bigint)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  total_quantity INTEGER;
BEGIN
  SELECT SUM(quantity) AS total_quantity
  INTO total_quantity
  FROM public.grocerystoreitems 
  WHERE "storeId" = storeId AND quantity > 0;

  UPDATE grocerystores
  SET quantity = total_quantity
  WHERE Id = storeId;
END;
$$;
x
Still gives me the error. The quantity column on both tables is "numeric" if that matters

https://cdn.discordapp.com/attachments/1111459092783968317/1111462662111510538/Screenshot_2023-05-25_at_8.15.08_PM.png▾

https://cdn.discordapp.com/attachments/1111459092783968317/1111462662417690674/Screenshot_2023-05-25_at_8.14.03_PM.png▾

g
Every where you have capital letters for names of columns/tables has to be in double quotes ... "Id". It is a bad idea to use capital letters in Postgres.
x
Okay, I have been using bard to help me with this function so that is what is came out with. However I just got it replaced with all lower case.
Copy code
sql
create or replace function update_quantity(storeid bigint)
returns void
language plpgsql as $$
declare
  total_quantity numeric;
begin
  select sum(quantity) as total_quantity
  into total_quantity
  from public.grocerystoreitems 
  where "storeid" = storeid and quantity > 0;

  update grocerystores
  set quantity = total_quantity
  where id = storeid;
end;
$$;
g
That also means your table column names have to all change
x
really, they are all lowercase currently. Are you saying to change them to uppercase or am I good?

https://cdn.discordapp.com/attachments/1111459092783968317/1111463949012058232/Screenshot_2023-05-25_at_8.20.07_PM.png▾

https://cdn.discordapp.com/attachments/1111459092783968317/1111463949758648391/Screenshot_2023-05-25_at_8.20.12_PM.png▾

g
Also you should not have a parameter with the same name as a column you plan to compare it to (storeid) If you do you may have to use table.storeid to clarify. Also int8 is not the same as integer if you try and compare them.
I still see caps in your table columns... but you might not be using those columns, or you have them in "storeId" in the function. It is just a mess to deal with it that way.
Also, also your 
sum(numeric col) as var
I don't think you also then use 
INTO var
after it.

https://cdn.discordapp.com/attachments/1111459092783968317/1111466787402420224/image.png▾

x
why can't I change the parameter name? the actual column name is "storeId" not "storeid", why doesn't it enforce the camel case one?

https://cdn.discordapp.com/attachments/1111459092783968317/1111466999109922846/Screenshot_2023-05-25_at_8.31.16_PM.png▾

g
You have to drop the function to change return or parameters.
"storeId" will work if the column name is storeId. It is just bad practice to do that in Postgres and makes all of this more difficult. An you will forget at somepoint and spend an hour trying to figure out what is wrong.
x
Okay I have changed to be a bit more generic with the param name to be "store"
Copy code
sql
create or replace function update_quantity(store int8)
returns void
language plpgsql as $$
declare
  total numeric;
begin
  select sum(quantity) as total
  from public.grocerystoreitems 
  where "storeId" = store and quantity > 0;

  update grocerystores
  set quantity = total
  where id = store;
end;
$$;
when I run this it says "Success. No rows returned". So maybe that is all the problems with the actual function, but unfortunately when I run update_quantity(46) I keep getting invalid SQL 😔
g
Are you just typing that in the SQL editor? If so try 
select update....
x
yes I am, okay this is a new error🤔

https://cdn.discordapp.com/attachments/1111459092783968317/1111470877981298819/Screenshot_2023-05-25_at_8.48.01_PM.png▾

g
OK a pot shot,
Copy code
declare
  total numeric;
begin
  select sum(quantity) as total1
  into total
  from public.grocerystoreitems
That passes my IDE syntax checker because you are not using the same name for as and into. But still seems funky.
x
Yayy!! it works now. Now for the return I get this. I don't necessarily care to get that total value back but it might be good incase I need to call it in my app. 1. Would that be as easy as changing the returns type and doing RETURN total at the end of my SQL Statement in the function? 2. Now that this is working It should be easy to implement a trigger using the UI and passing in the storeId because that is where I want to set the trigger on.

https://cdn.discordapp.com/attachments/1111459092783968317/1111476258115682374/Screenshot_2023-05-25_at_9.07.19_PM.png▾

https://cdn.discordapp.com/attachments/1111459092783968317/1111476258627399760/Screenshot_2023-05-25_at_9.07.24_PM.png▾

g
You can just return the result if you change void
x
Okay I finally got it all working 😎 . The supabase AI was pretty neat and helped me get to the final solution. I had to make two function and two triggers. One to deal with inserts and updates. The other set deals with deletes. But it also fires before a delete. It also handles edge cases like if there is only 1 item in the search and we delete it. anyway thanks for your help I appreciate it👍 here is the code if you are interested lol.
Copy code
sql

-- Drop the trigger if it exists
DROP TRIGGER IF EXISTS update_quantity_trigger ON public.grocerystoreitems;

-- Drop the function if it exists
DROP FUNCTION IF EXISTS update_quantity();

-- Recreate the function
CREATE OR REPLACE FUNCTION update_quantity()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  total numeric;
BEGIN
  RAISE NOTICE 'update_quantity called';
  SELECT sum(quantity) AS total1
  INTO total
  FROM public.grocerystoreitems
  WHERE "storeId" = NEW."storeId"
  UPDATE grocerystores
  SET quantity = total
  WHERE id = NEW."storeId";
  RETURN NEW;
END;
$$;

-- Recreate the trigger
CREATE TRIGGER update_quantity_trigger
AFTER INSERT OR UPDATE OR DELETE ON public.grocerystoreitems
FOR EACH ROW
EXECUTE FUNCTION update_quantity();
Copy code
sql

-- Drop the trigger if it exists
DROP TRIGGER IF EXISTS before_delete_quantity_trigger ON public.grocerystoreitems;

-- Drop the function if it exists
DROP FUNCTION IF EXISTS before_delete_quantity();

-- Recreate the function with a new name
CREATE OR REPLACE FUNCTION before_delete_quantity()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  total numeric;
BEGIN
  RAISE NOTICE 'before_delete_quantity called';
  SELECT count(*) AS count1
  INTO total
  FROM public.grocerystoreitems
  WHERE "storeId" = OLD."storeId";
  IF total = 1 THEN
    total := 0;
  ELSE
    SELECT sum(quantity) AS total1
    INTO total
    FROM public.grocerystoreitems
    WHERE "storeId" = OLD."storeId" AND id != OLD.id;
  END IF;
  UPDATE grocerystores
  SET quantity = total
  WHERE id = OLD."storeId";
  RETURN OLD;
END;
$$;

-- Recreate the trigger with the new function name
CREATE TRIGGER before_delete_quantity_trigger
BEFORE DELETE ON public.grocerystoreitems
FOR EACH ROW
EXECUTE FUNCTION before_delete_quantity();
PreviousNext
Powered by