Select-into, count error (stored function).

We have the following Function (Still building it up)
We call it with the following SQL

SELECT * FROM sp_report_retail_sales ('{1,2,3}', 3, to_timestamp ('10-02-2003', 'DD-MM-YYYY'), to_timestamp ('14-02-2003', 'DD-MM-YYYY'), true);

The top query (size = 0) returns a result set, but when the "count (tbl_tranitem.fld_tranitem_productid)"
line is left in the second query (ELSE query), we get the following error

WARNING:  Error occurred while executing PL/pgSQL function sp_report_retail_sales
WARNING:  line 40 at select into variables
ERROR:  parser: parse error at or near "(" at character 69

If we comment this line out, it works and we get the following output

NOTICE:  Found 1
NOTICE:  Not Found 2
NOTICE:  Not Found 3
 prdcrd_id | title | num_sales | base_total | gst_total
-----------+-------+-----------+------------+-----------
(0 rows)

CREATE OR REPLACE FUNCTION sp_report_retail_sales(int8[], int8, timestamp with time zone, timestamp with time zone, boolean) RETURNS SETOF sp_report_retail_sales_type AS '

DECLARE
-------[Parameters]-------
        prod_id ALIAS FOR $1;
        size ALIAS FOR $2;
        start_date ALIAS FOR $3;
        end_date ALIAS FOR $4;
        include_soh ALIAS FOR $5;

-------[Variables]--------
        counter int8;
        title text;
        retset record;
BEGIN

        IF size = 0 THEN
                FOR retset IN
               
                        SELECT  tbl_prdcrd.fld_prdcrd_id,
                                tbl_prdcrd.fld_prdcrd_title,
                                count (tbl_tranitem.fld_tranitem_productid),
                                sum (tbl_tranitem.fld_tranitem_price * tbl_tranitem.fld_tranitem_quantity),
                                sum (tbl_tranitem.fld_tranitem_price * tbl_tranitem.fld_tranitem_quantity * tbl_tranitem.fld_tranitem_gst)

                        FROM    tbl_prdcrd INNER JOIN tbl_tranitem ON tbl_prdcrd.fld_prdcrd_id = tbl_tranitem.fld_tranitem_productid

                                INNER JOIN tbl_tran ON tbl_tranitem.fld_tranitem_transactionid = tbl_tran.fld_tran_id
                        WHERE   tbl_tranitem.fld_tranitem_type = 1 AND
                                tbl_tran.fld_tran_date > start_date AND
                                tbl_tran.fld_tran_date < end_date
                        GROUP BY tbl_prdcrd.fld_prdcrd_id,
                        tbl_prdcrd.fld_prdcrd_type,
                        tbl_prdcrd.fld_prdcrd_title
                                                        LOOP
               
                        RETURN NEXT retset;
               
                END LOOP;
       
        ELSE
                FOR count IN 1..size LOOP
                       
                        SELECT  tbl_prdcrd.fld_prdcrd_id,
                                tbl_prdcrd.fld_prdcrd_title,
                ->              count (tbl_tranitem.fld_tranitem_productid),     <-
                                sum (tbl_tranitem.fld_tranitem_price * tbl_tranitem.fld_tranitem_quantity),
                                sum (tbl_tranitem.fld_tranitem_price * tbl_tranitem.fld_tranitem_quantity * tbl_tranitem.fld_tranitem_gst)

 
                        INTO    retset
                        FROM    tbl_prdcrd INNER JOIN tbl_tranitem ON tbl_prdcrd.fld_prdcrd_id = tbl_tranitem.fld_tranitem_productid

                                INNER JOIN tbl_tran ON tbl_tranitem.fld_tranitem_transactionid = tbl_tran.fld_tran_id
                        WHERE   tbl_tranitem.fld_tranitem_type = 1 AND
                                tbl_prdcrd.fld_prdcrd_id = prod_id[count] AND
                                tbl_tran.fld_tran_date > start_date AND
                                tbl_tran.fld_tran_date < end_date
                        GROUP BY tbl_prdcrd.fld_prdcrd_id,
                                tbl_prdcrd.fld_prdcrd_type,
                                tbl_prdcrd.fld_prdcrd_title;

                        IF NOT FOUND THEN
                                RAISE NOTICE ''Not Found %'', count;
                        ELSE
                                RAISE NOTICE ''Found %'', count;

                        END IF;
                END LOOP;
       
        END IF;

        RETURN;
END;
'  LANGUAGE 'plpgsql' IMMUTABLE;
,

Mike Weaver
Software Developer

5, 42 Ladner Street
O'Connor, WA, 6163
All correspondence:
PO Box Y3502
East St Georges Terrace
Perth WA 6832

P: (+618) 9331 2700
F: (+618) 9331 3733
M: 0403 385 181
W: http://www.corpusglobe.com/
E: mweaver@corpusglobe.com

This email is intended only for the use of the individual or entity named above and may contain information that is confidential. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. When addressed to our clients, any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing Corpus Globe client engagement letter. If you have received this Email in error, please notify us immediately by return email or telephone +61 8 9331 2700 and destroy the original message. Thank You.