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
