X-Original-To: pgsql-novice@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DBEEC474E4F; Thu, 13 Feb 2003 22:54:44 -0500 (EST) Received: from vision1.touristvision.com.au (vision1.corpusglobe.com [202.61.128.2]) by postgresql.org (Postfix) with ESMTP id CA100474E42; Thu, 13 Feb 2003 22:54:42 -0500 (EST) Received: by VISION1 with Internet Mail Service (5.5.2653.19) id ; Fri, 14 Feb 2003 12:09:51 +0800 Message-ID: <3B663B41B350D311AEEF00A0C9254563201A70@VISION1> From: Michael Weaver To: "'pgsql-novice@postgresql.org'" , "'pgsql-sql@postgresql.org'" Subject: Passing arrays Date: Fri, 14 Feb 2003 12:09:44 +0800 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2D3DE.E7A277A0" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/168 X-Sequence-Number: 6184 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C2D3DE.E7A277A0 Content-Type: text/plain; charset="iso-8859-1" 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. 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 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. ------_=_NextPart_001_01C2D3DE.E7A277A0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Passing arrays

How do you get an array of elements to be passed to a sto= red 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<= /FONT>
            &nbs= p;   SELECT  tbl_prdcrd.fld_prdcrd_id,
            &nbs= p;           co= unt (tbl_tranitem.fld_tranitem_productid) as num_sales,
            &nbs= p;           su= m (tbl_tranitem.fld_tranitem_price * tbl_tranitem.fld_tranitem_quantity) as= base_total,
            &nbs= p;           su= m (tbl_tranitem.fld_tranitem_price * tbl_tranitem.fld_tranitem_quantity * t= bl_tranitem.fld_tranitem_gst) as gst_total

             = ;   FROM    tbl_prdcrd INNER JOIN t= bl_tranitem ON tbl_prdcrd.fld_prdcrd_id =3D tbl_tranitem.fld_tranitem_produ= ctid

             = ;           INN= ER JOIN tbl_tran ON tbl_tranitem.fld_tranitem_transactionid =3D tbl_tran.fl= d_tran_id
            &nbs= p;   WHERE   tbl_prdcrd.fld_prdcrd_id IN= (prod_id)
            &nbs= p;   GROUP BY tbl_prdcrd.fld_prdcrd_id,
            &nbs= p;   tbl_prdcrd.fld_prdcrd_type
          &= nbsp;             &n= bsp;            &nbs= p;          LOOP
       =20
            &nbs= p;   RETURN NEXT retset;
            &nbs= p;  =20
        END LOOP;
       =20
        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 fu= nction sp_report_retail_sale
s_01
WARNING:  line 8 at for over select rows
ERROR:  Unable to identify an operator '=3D' for ty= pes '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 r= esult 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 disse= mination, distribution or copying of this email is strictly prohibited. Whe= n addressed to our clients, any opinions or advice contained in this email = are subject to the terms and conditions expressed in the governing Corpus G= lobe client engagement letter. If you have received this Email in error, pl= ease notify us immediately by return email or telephone +61 8 9331 2700 and= destroy the original message. Thank You.


------_=_NextPart_001_01C2D3DE.E7A277A0--