Syntax error in stored proceedure

Code check, I can't find it

Page 1 of 1

4 Replies - 724 Views - Last Post: 29 June 2010 - 07:09 AM Rate Topic: -----

#1 supersssweety  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 22
  • View blog
  • Posts: 373
  • Joined: 16-March 07

Syntax error in stored proceedure

Posted 28 June 2010 - 11:34 AM

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';
            [b]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;[/b]
        --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;


The code just runs a query that selects from this function. I think the reason why I can't get it to reproduce is because there are other things that happen upon page load to the tables that are processed here. I only get the error in the code when the buy_object query returns results. The bolded part is what is causing the error.

ohhh I think i found it...

nope

Is This A Good Question/Topic? 0
  • +

Replies To: Syntax error in stored proceedure

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5881
  • View blog
  • Posts: 12,758
  • Joined: 16-October 07

Re: Syntax error in stored proceedure

Posted 28 June 2010 - 12:20 PM

Nothing jumps out.

However, unions are usually evil. I'd write the one query like so:
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
		LEFT OUTER JOIN promo_objects po on po.object_id=pbo.object_id
		LEFT OUTER JOIN promo_get_objects pgo on pgo.object_id=pbo.object_id
	WHERE pco.cart_id = p_cart_id
		and (po.object_id is not null or pgo.object_id is not null)



Loops are also evil, but I don't know enough Postgres syntax ( read none ) to tackle that one.
Was This Post Helpful? 1
  • +
  • -

#3 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6076
  • View blog
  • Posts: 23,543
  • Joined: 23-August 08

Re: Syntax error in stored proceedure

Posted 28 June 2010 - 12:26 PM

Your quantity and discount_amount are in reverse order for the type.

SELECT DISTINCT ON(pbo.object_id)
  pbo.object_id, p.promo_id, p.buy_quantity, p.get_quantity, pco.discount_amount, co.quantity


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));

Was This Post Helpful? 1
  • +
  • -

#4 supersssweety  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 22
  • View blog
  • Posts: 373
  • Joined: 16-March 07

Re: Syntax error in stored proceedure

Posted 28 June 2010 - 03:06 PM

AH you're awesome JackOfAllTrades :*! YAY it gives me the complete wrong results I am looking for but NO ERROR YOU ROCK \m/ Thank you! I knew it was something stupid.
Was This Post Helpful? 0
  • +
  • -

#5 supersssweety  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 22
  • View blog
  • Posts: 373
  • Joined: 16-March 07

Re: Syntax error in stored proceedure

Posted 29 June 2010 - 07:09 AM

TY baavagi I will use that
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1