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 )