This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: pgsql-sql@postgresql.org
From: Michael Weaver <mweaver@corpusglobe.com>
Subject: [SQL] Passing arrays
Date: Fri, 14 Feb 2003 12:09:44 +0800
How do you get an array of elements to be passed to a stored proc such that
you can use the array in a SELECT statement in the WHERE clause
- e.g. WHERE field IN (array) etc...
CREATE OR REPLACE FUNCTION sp_report_retail_sales(int8[]) RETURNS SETOF
sp_report_retail_sales_type AS '
DECLARE
-------[Parameters]-------
prod_id ALIAS FOR $1;
-------[Variables]--------
retset record;
BEGIN
FOR retset IN
SELECT tbl_prdcrd.fld_prdcrd_id,
count (tbl_tranitem.fld_tranitem_productid) as
num_sales,
sum (tbl_tranitem.fld_tranitem_price *
tbl_tranitem.fld_tranitem_quantity) as base_total,
sum (tbl_tranitem.fld_tranitem_price *
tbl_tranitem.fld_tranitem_quantity * tbl_tranitem.fld_tranitem_gst) as
gst_total
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_prdcrd.fld_prdcrd_id IN (prod_id)
GROUP BY tbl_prdcrd.fld_prdcrd_id,
tbl_prdcrd.fld_prdcrd_type
LOOP
RETURN NEXT retset;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
We were calling the procedure as follows
select * from sp_report_retail_sales1 ('{1,3}');
We keep getting issues with casting.
<snip>
WARNING: Error occurred while executing PL/pgSQL function
sp_report_retail_sale
s_01
WARNING: line 8 at for over select rows
ERROR: Unable to identify an operator '=' for types 'bigint' and 'bigint[]'
You will have to retype this query using an explicit cast
</snip>
Trying the parameter as text worked, but gave 0 rows in result set.
===
To: pgsql-novice@postgresql.org
From: Josh Berkus <josh@agliodbs.com>
Subject: Re: [NOVICE] [SQL] Passing arrays
Date: Thu, 13 Feb 2003 20:14:15 -0800
Micheal,
> How do you get an array of elements to be passed to a stored proc such that
> you can use the array in a SELECT statement in the WHERE clause
> - e.g. WHERE field IN (array) etc...
There's no easy way, right now. You'll have to:
1) loop through the array and pass its elements to a comma-delimited TEXT
variable.
2) Build up your query as a dynamic string.
3) Do your query loop as a FOR record IN EXECUTE dynamic_query_string
===