Обсуждение: Calling a plpgsql function with composite type as parameter?

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;

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 )

On Thu, Jan 14, 2010 at 8:50 PM, Jamie Begin <jjbegin@gmail.com> 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 can built it via string.  however, python has a really fabulous
driver (at least, on paper, I don't code python!) that supports arrays
and composites natively over the protocol.  Take a look here:

http://python.projects.postgresql.org/docs/0.9/driver.html#type-support

And read the chapter carefully.  If this fits your use case, it might
allow you to not build from string, which absolutely awful route if
you make heavy use of arrays/composites.

merlin