Thread: 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.
Michael Weaver <mweaver@corpusglobe.com> writes:
> 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),
I think the problem is that you've used "count" as the loop variable
name. The plpgsql parser is probably substituting a variable reference
for the "count" that you intend as an aggregate function name. Use a
different name for the loop variable.
[ beware, off-topic rant ahead ]
> 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.
Do your company's lawyers really expect anyone to take this seriously in
a message sent to a public mailing list? Perhaps I shall call that
number and tell them they look like utter fools.
regards, tom lane