Python DataError coming from stored procedure, but no error when run manually.

Question

I am getting this error:

DataError: (DataError) invalid input syntax for integer: "1.50" CONTEXT: PL/pgSQL function "sp_aggregate_cart" line 82 at FOR over EXECUTE statement 'SELECT total_items, subtotal, is_shipping_required, discount_other, is_shipping_discount FROM sp_aggregate_cart(8135)' {}

When running my application code. When I run that query manually, everything is fine. I happen to know the 1.50 is in this instance, and it is a value that is passed through a variable in the function that is declared as numeric(10,2). It is NOT returned by the function, just processed.

How can this query throw this error in the application code, but run fine in pgadmin?

Ok Here is the stored prodcedure...the discount_amount is the 1.50 you see the error on

CREATE TYPE buy_object_info as (object_id integer, promo_id integer, buy_quantity integer, get_quantity integer, quantity integer, discount_amount numeric(10,2));

-- Function: sp_aggregate_cart(integer)

DROP FUNCTION sp_aggregate_cart(integer);

CREATE OR REPLACE FUNCTION sp_aggregate_cart(p_cart_id integer)
  RETURNS SETOF cart AS
$BODY$
DECLARE
    v_total_items int;
    v_subtotal numeric;
    v_discount_other record;
    v_items_shipping integer;
    v_shipping_required boolean;
    buy_object_info buy_object_info%rowtype;
    buy_object_price numeric;
    buy_object_orginal_subtotal numeric;
    other_promo_id integer;
    buy_object_query text;
    other_promo_buy_quantity integer;
    BEGIN
    -- Get the total number of items
    SELECT sum(quantity) INTO v_total_items FROM cart_object WHERE cart_id = p_cart_id;

    -- Get the subtotal
    SELECT sum(unit_price) INTO v_subtotal FROM (
        SELECT co.cart_id, co.object_id, co.quantity, 
            CASE 
                --When buy only cart quantity = buy quantity
                WHEN (p.get_quantity = 0 or p.get_quantity IS NULL) AND (p.buy_quantity > 0 OR p.buy_quantity IS NOT NULL) AND pbo.object_id = co.object_id AND p.buy_quantity = co.quantity
                    THEN ((cast(oa.value AS numeric(10,2)) - COALESCE(pco.discount_amount, 0)) * p.buy_quantity)
                --When buy only more than the buy quantity in cart
                WHEN (p.get_quantity = 0 or p.get_quantity IS NULL) AND (p.buy_quantity > 0 OR p.buy_quantity IS NOT NULL) AND pbo.object_id = co.object_id AND p.buy_quantity < co.quantity
                    THEN (((cast(oa.value AS numeric(10,2)) - COALESCE(pco.discount_amount, 0))) * p.buy_quantity) + ((co.quantity - p.buy_quantity) * (cast(oa.value AS numeric(10,2))))
                --When buy/get
                WHEN (p.get_quantity > 0 or p.get_quantity IS NOT NULL) AND (p.buy_quantity > 0 OR p.buy_quantity IS NOT NULL) AND pgo.object_id = co.object_id                   
                    THEN ((cast(oa.value AS numeric(10,2)) - COALESCE(pco.discount_amount, 0)) + ((co.quantity - 1) * cast(oa.value AS numeric(10,2))))
                WHEN (p.get_quantity = 0 or p.get_quantity IS NULL) AND (p.buy_quantity = 0 OR p.buy_quantity IS NULL)
                    THEN ((cast(oa.value AS numeric(10,2)) - COALESCE(pco.discount_amount, 0)) * co.quantity)
                ELSE
                    (cast(oa.value AS numeric(10,2)) * co.quantity)
            END AS "unit_price"     
            FROM cart_object co
            JOIN object_attr oa ON oa.object_id=co.object_id AND oa.attr_id=50
            LEFT JOIN promo_cart_objects pco ON pco.cart_id=co.cart_id AND pco.object_id=co.object_id
            LEFT JOIN promos p ON p.promo_id=pco.promotion_id
            LEFT JOIN promo_get_objects pgo ON pgo.object_id = co.object_id AND pgo.promo_id = pco.promotion_id
            LEFT JOIN promo_buy_objects pbo ON pbo.object_id = co.object_id AND pbo.promo_id = pco.promotion_id
        WHERE co.cart_id=p_cart_id
        GROUP BY co.cart_id, co.object_id, oa.value, co.quantity, p.get_quantity, p.buy_quantity, pbo.object_id, pgo.object_id, pco.discount_amount, p.promo_id
    ) AS a;
    --Get the buyobjects that are in the cart and need to have their line item subtotal recalculated
    -- :)
    buy_object_query := 
        'SELECT DISTINCT ON(pbo.object_id) 
            pbo.object_id, p.promo_id, p.buy_quantity, p.get_quantity, pco.discount_amount, co.quantity
        FROM 
            promo_buy_objects pbo 
        JOIN 
            promos p on p.promo_id = pbo.promo_id AND p.active = TRUE AND now() BETWEEN p.start_date AND p.end_date 
        JOIN 
            promo_cart_objects pco ON pco.object_id = pbo.object_id 
        JOIN 
            cart_object co ON co.cart_id = pco.cart_id AND co.object_id = pbo.object_id
        WHERE 
            pco.cart_id = ' || p_cart_id || '
            AND 
            pbo.object_id IN(SELECT 
                        po.object_id 
                    FROM 
                        promo_objects po 
                    JOIN 
                        promos p on p.promo_id = po.promotion_id AND p.active = TRUE AND now() BETWEEN p.start_date AND p.end_date 
                    JOIN 
                        promo_cart_objects pco ON pco.object_id = po.object_id 
                    WHERE 
                        pco.cart_id = ' || p_cart_id || '
                    UNION 
                    SELECT 
                        pgo.object_id 
                    FROM 
                        promo_get_objects pgo 
                    JOIN 
                        promos p on p.promo_id = pgo.promo_id AND p.active = TRUE AND now() BETWEEN p.start_date AND p.end_date 
                    JOIN 
                        promo_cart_objects pco ON pco.object_id = pgo.object_id JOIN cart_object co ON co.cart_id = pco.cart_id 
                    WHERE pco.cart_id = ' || p_cart_id || ')
            AND
            co.quantity > p.buy_quantity';
        FOR buy_object_info IN EXECUTE buy_object_query LOOP
            --Get the price
            SELECT cast("value" as numeric(10,2)) INTO buy_object_price FROM object_attr WHERE object_id = buy_object_info.object_id AND attr_id = 50;
            --What was that original price? Redundant I know ...I might get around to optimizing this function
            IF (buy_object_info.get_quantity = 0 or buy_object_info.get_quantity IS NULL) AND (buy_object_info.buy_quantity > 0 OR buy_object_info.buy_quantity IS NOT NULL) AND buy_object_info.buy_quantity = buy_object_info.quantity
                THEN buy_object_orginal_subtotal := ((buy_object_price - COALESCE(buy_object_info.discount_amount, 0)) * buy_object_info.buy_quantity);
            --When buy only more than the buy quantity in cart
            ELSIF (buy_object_info.get_quantity = 0 or buy_object_info.get_quantity IS NULL) AND (buy_object_info.buy_quantity > 0 OR buy_object_info.buy_quantity IS NOT NULL) AND buy_object_info.buy_quantity < buy_object_info.quantity
                THEN buy_object_orginal_subtotal := (((buy_object_price - COALESCE(buy_object_info.discount_amount, 0))) * buy_object_info.buy_quantity) + ((buy_object_info.quantity - buy_object_info.buy_quantity) * (buy_object_price));
            --When buy/get
            ELSIF (buy_object_info.get_quantity > 0 or buy_object_info.get_quantity IS NOT NULL) AND (buy_object_info.buy_quantity > 0 OR buy_object_info.buy_quantity IS NOT NULL)               
                THEN buy_object_orginal_subtotal := ((buy_object_price - COALESCE(buy_object_info.discount_amount, 0)) + ((buy_object_info.quantity - 1) * buy_object_price));
            ELSIF (buy_object_info.get_quantity = 0 or buy_object_info.get_quantity IS NULL) AND (buy_object_info.buy_quantity = 0 OR buy_object_info.buy_quantity IS NULL)
                THEN buy_object_orginal_subtotal := ((buy_object_price - COALESCE(buy_object_info.discount_amount, 0)) * buy_object_info.quantity);
            ELSE
                buy_object_orginal_subtotal := (cast(oa.value AS numeric(10,2)) * buy_object_info.quantity);
            END IF;
            --Well now we need that other promotion...this is so lame
             SELECT INTO other_promo_id, other_promo_buy_quantity promo_id, buy_quantity FROM(
                    SELECT 
                        p.promo_id AS promo_id, p.buy_quantity AS buy_quantity
                    FROM 
                        promo_objects po 
                    JOIN 
                        promos p on p.promo_id = po.promotion_id AND p.active = TRUE AND now() BETWEEN p.start_date AND p.end_date 
                    JOIN 
                        promo_cart_objects pco ON pco.object_id = po.object_id 
                    WHERE 
                        pco.cart_id = p_cart_id
                    UNION 
                    SELECT 
                        p.promo_id AS promo_id, p.buy_quantity AS buy_quantity
                    FROM 
                        promo_get_objects pgo 
                    JOIN 
                        promos p on p.promo_id = pgo.promo_id AND p.active = TRUE AND now() BETWEEN p.start_date AND p.end_date 
                    JOIN 
                        promo_cart_objects pco ON pco.object_id = pgo.object_id JOIN cart_object co ON co.cart_id = pco.cart_id 
                    WHERE pco.cart_id = p_cart_id AND pco.object_id = buy_object_info.object_id) AS foo;
            --Alrighty now that we have everything we need, let's perform this funky ass math
            v_subtotal := v_subtotal - buy_object_orginal_subtotal;
            v_subtotal := v_subtotal + (other_promo_buy_quantity * buy_object_price) + ((buy_object_info.quantity - other_promo_buy_quantity) * (buy_object_price - COALESCE(buy_object_info.discount_amount, 0)));
        END LOOP;
    --Get Discount Other
    --SELECT COALESCE(max(discount_amount), 0), is_shipping_discount INTO v_discount_other FROM cart_promotion WHERE cart_id=p_cart_id;
    SELECT COALESCE(discount_amount, 0) as discount, COALESCE(is_shipping_discount, false) as is_shipping_discount INTO v_discount_other
    FROM promo_carts WHERE cart_id=p_cart_id order by discount_amount desc limit 1;

    -- Determine if shipping is required
    SELECT count(*) INTO v_items_shipping
    FROM object o, object_attr oa, cart_object co
    WHERE oa.object_id = o.object_id AND co.object_id = o.object_id 
    AND attr_id = 74 and cart_id = p_cart_id AND oa.value = 'true';

    IF v_items_shipping > 0 THEN
        v_shipping_required := True;
    ELSE 
        v_shipping_required := False;
    END IF;

    -- Update the cart
    UPDATE cart SET 
        total_items = COALESCE(v_total_items, 0), 
        subtotal = COALESCE(v_subtotal, 0),
        is_shipping_required = v_shipping_required,
        discount_other = COALESCE(v_discount_other.discount, 0),
        is_shipping_discount = COALESCE(v_discount_other.is_shipping_discount, false)
    WHERE id = p_cart_id;

    RETURN QUERY SELECT * from cart WHERE id = p_cart_id;
    END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION sp_aggregate_cart(integer) OWNER TO postgres;

Answer 1

the buy_object_query was selecting things in the wrong order. It selects into the type I created at the beginning of that code buy_object_info. I was selecting the decimal into the integer