Thread: Passing arrays

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.

,

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.

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

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Fri, 14 Feb 2003, Michael Weaver wrote:

> 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...

A straight WHERE field IN (array) doesn't work.  That's attempting to
compare the field directly to the array as a whole (which is why you get
the particular error you do.

You might want to look at contrib/array for some loadable
functions/operators in working with arrays.