I'm working on an e-commerce site that calls various plpgsql functions
from a Python app. One of the things I need to do is create a
shopping cart and add several items to it. I'd like for both of these
steps to be contained within the same transaction so if an error
occurs adding an item to the cart, the entire cart creation is rolled
back. I'm attempting to use something like the code below (I've
simplified it). However, a) I'm not sure if this is the correct
architectural decision and b) I haven't been able to figure how how to
call this function using a composite type (my "_cart_contents") as a
parameter. I'd greatly appreciate any suggestions. Thanks!
CREATE TABLE carts (id serial, cart_owner varchar, cart_name varchar);
CREATE TABLE carts_items (id serial, cart_id int REFERENCES carts(id),
product_name varchar, price decimal(5,2) );
CREATE TYPE cart_item_type AS (product_name varchar, price decimal(5,2));
CREATE OR REPLACE FUNCTION cart_create(
_user_id int
,_cart_name varchar
,_cart_contents cart_item_type[]
) RETURNS bool AS $$
DECLARE
_cart_id int;
_id int;
_i int;
_n varchar;
_p decimal(5,2);
_product_id int;
BEGIN
INSERT INTO carts (cart_owner, cart_name)
VALUES (_user_id, _cart_name);
SELECT id INTO _cart_id FROM carts WHERE id = CURRVAL('carts_id_seq');
FOR _i IN COALESCE(array_lower(_cart_contents,1),0) ..
COALESCE(array_upper(_cart_contents,1),-1) LOOP
_n := _cart_contents[_i]['product_name'];
_p := _cart_contents[_i]['price'];
INSERT INTO cart_items (cart_id, product_name, price)
VALUES (_cart_id, _n, _p);
END LOOP;
RETURN True;
END; $$ LANGUAGE plpgsql;
