Supabase Update which subtracts from numeric field

Question

Using a Supabase database, I'm have having an issue with subtracting from a quantity total which exists in the database field. here is my code:

const { data, error } = await supabase
  .from('product')
  .update({ quantity: `quantity - ${Number(orderItem.quantity)}` })
  .eq('id', orderItem.id);

if (error){
  throw error;
}

The error being thrown is stating that a string can't be added to a numeric field type (which makes sense), i have also attempted the following:

.update({ quantity: quantity - Number(orderItem.quantity) })

Which outputs the following error: ReferenceError: quantity is not defined

I realise I could fetch the quantity from a prior database call, do the quantity equation then do a simple update with the result, however i'd like to know if it's possible in a single db call?

score 0 · Accepted Answer · answered May 04 '23 at 19:48

PostgREST doesn't have the quantity value in this context. You'll either have to fetch/select the current value before updating it:

const { data: result, error } = await supabase
  .from('product')
  .select('quantity')
  .eq('id', orderItem.id);

const { data, error } = await supabase
  .from('product')
  .update({ quantity: `${Number(result[0].quantity)} - ${Number(orderItem.quantity)}` })
  .eq('id', orderItem.id);

if (error){
  throw error;
}

Or you can create an SQL function to update this, then call this function from the client library:

CREATE FUNCTION reduce_product_quantity(p_product_id INTEGER, p_reduce_quantity INTEGER)
RETURNS VOID
AS $$
BEGIN
    UPDATE products
    SET quantity = quantity - p_reduce_quantity
    WHERE id = p_product_id;
END;
$$ LANGUAGE plpgsql;

Then, call it using the client library:

const { data: ret, error } = await supabase
   .rpc('reduce_product_quantity', 
   {
      "p_product_id": orderItem.id, 
      "p_reduce_quantity" : orderItem.quantity
    });

Supabase Update which subtracts from numeric field

1 Answers1