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(at)corpusglobe(dot)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.