Re: Calling a plpgsql function with composite type as parameter?

Jamie Begin wrote:
> 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;

You should probably have quantity in there also. But here's how you
would call the function:

SELECT cart_create(123, 'Scotts Cart',
array[('foo', 12.25),('bar', 13.99)]::_cart_item_type )