https://supabase.com/ logo
Join Discord
Powered by
Does anybody see something wrong with this functio...
# help
j
Does anybody see something wrong with this function?
Copy code
sql
BEGIN
  IF NOT EXISTS(SELECT 1 FROM cart WHERE user_id = auth.uid()) THEN
    INSERT INTO cart (user_id, total) VALUES (auth.uid(), 0);
    INSERT INTO cart_item (cart_id, product_id, user_id, quantity)
    VALUES (
      new.id,
      new_product_id,
      auth.uid(),
      new_quantity
    );
  ELSE
    UPDATE cart SET total = old.total + new_quantity * (SELECT price FROM product WHERE id = new_product_id)
    WHERE user_id = auth.uid();

    UPDATE cart_item 
    SET quantity = old.quantity + new_quantity 
    WHERE product_id = new_product_id AND user_id = auth.uid();
  END IF;

  RETURN new;
END;
n
Hello @João Vitor! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! 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.
g
Can you give a clue what is happening?
j
"Could not find the public.insert_item_to_cart(product_id, quantity) function or the public.insert_item_to_cart function with a single unnamed json or jsonb parameter in the schema cache"
g
Is that from an rpc call?
j
Yes
g
Normally you use "new"" in a trigger function. Here you say you want to return a record I don't see a select at quick glance. But your error is saying maybe you are calling the function with with the wrong parameters. What is your rpc call
j
I got it to work at first but when I tried to update the function it started to throw this error
Copy code
js
await supabase.rpc('insert_item_to_cart', {
  product_id: 'some-uuid',
  quantity: 3,
})
I saw that the 
new
and 
old
keywords work whenever there's a 
INSERT
UPDATE
or 
DELETE
IDK if that's true tho
g
Only on trigger functions. for new old.
You are not using the correct parameter names in your rpc call.
j
Oops, you're right
I got it to call the function, now I'm getting this error "missing FROM-clause entry for table "new""
If I change this to a trigger function, can I still call it with rpc?
g
No. But where are you expecting new to come from? Normally new is the data record being inserted/updated in the table the trigger is on.
j
I thought the 
new
was the inserted data
g
What inserted data?
j
INSERT INTO cart (user_id, total) VALUES (auth.uid(), 0);
I thought 
new
here would be the new 
cart
record inserted in the table
And so it would have 
new.id
as the 
cart.id
g
You have to provide the data to be inserted. New/Old is only for inputing data to a trigger function.
j
How can I provide this data?
I might do another SELECT after inserting to get the new cart.id
g
I have no idea what the data is supposed to be. Probably as a parameter to the rpc... Yes you could do a select to get data from another table. But just forget about new/old concept except that is the input provided to just a trigger function as it is the only way to pass data to it.
j
Okay
Do you think there's another way to get the new 
cart.id
after this line ```sql INSERT INTO cart (user_id, total) VALUES (auth.uid(), 0); ```Without doing another select?
g
So you want the cart id that insert auto generated? Yes, hang on.
j
Exactly
I'm seeing this on stackoverflow
Copy code
sql
INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');
Or 
SCOPE_IDENTITY()
g
https://www.postgresql.org/docs/current/sql-insert.html You can use returning. Maybe something like returning id as new_cart_id. But I've never used it.
j
Okay
I'll try that
Copy code
sql
DECLARE new_cart_item cart_item;
DECLARE new_cart_id uuid;

BEGIN
  IF NOT EXISTS(SELECT 1 FROM cart WHERE user_id = auth.uid()) THEN
    INSERT INTO cart (user_id, total) VALUES (auth.uid(), 0)
    RETURNING id INTO new_cart_id;
    INSERT INTO cart_item (cart_id, product_id, user_id, quantity)
    VALUES (
      new_cart_id,
      new_product_id,
      auth.uid(),
      new_quantity
    ) RETURNING * INTO new_cart_item;
  ELSE
    UPDATE cart SET total = total + new_quantity * (SELECT price FROM product WHERE id = new_product_id)
    WHERE user_id = auth.uid();

    UPDATE cart_item 
    SET quantity = quantity + new_quantity 
    WHERE product_id = new_product_id AND user_id = auth.uid()
    RETURNING * INTO new_cart_item;
  END IF;

  RETURN new_cart_item;
END;
LOL
IT WORKED
HAHAHAHA
So the resulting was something like this
Copy code
js
var data = {
  cart_id: "d41373ff-c802-42f8-9436-5e02899cb0fb"
  created_at: "2022-06-17T15:13:38.484863+00:00"
  id: "693ac372-3d1c-4b3c-8e5d-f53134c8a54e"
  product_id: "a5e19558-650d-400e-86c9-a894a8e85ef0"
  quantity: 9
  updated_at: "2022-06-17T15:16:06.054431+00:00"
  user_id: "eb85eb1c-f9fe-45d2-9727-2a5e9d5cc5b9"
}
And I tried to 
.rpc().select('*, product(*)')
but got this error Verify that 'insert_item_to_cart' and 'product' exist in the schema 'public' and that there is a foreign key relationship between them. If a new relationship was created, try reloading the schema cache.
Could not find a relationship between 'insert_item_to_cart' and 'product' in the schema cache
g
There is no foreign key from an rpc function for product(*) to work. You need to get that as part of your function with a select or join if there is a foreign key in the product table.
j
@garyaustin what's the output SQL for this 
supabase.from('cart').select('*, items:cart_item(*)')
?
And also, do you think a Prisma client would help me do those things easily? Like that function above
g
I know nothing about using Prisma, only dealing with the issues that come up here with SB. I think you are looking for an inner join https://www.tutorialspoint.com/postgresql/postgresql_using_joins.htm (sample here), but you probably want to ask in the sql thread as I always have to google for specifics of joins.
j
Thank you!
g
I see you did... LOL
n
Thread was archived by @João Vitor. Anyone can send a message to unarchive it.
PreviousNext
Powered by