Passing arrays

From mweaver@corpusglobe.com Fri Feb 14 03:54:44 2003 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-- From josh@agliodbs.com Fri Feb 14 04:16:46 2003 X-Original-To: pgsql-novice@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8CC25475C14; Thu, 13 Feb 2003 23:16:46 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 8F5FC475F73; Thu, 13 Feb 2003 23:14:36 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2839316; Thu, 13 Feb 2003 20:14:48 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Michael Weaver , "'pgsql-novice@postgresql.org'" , "'pgsql-sql@postgresql.org'" Subject: Re: [SQL] Passing arrays Date: Thu, 13 Feb 2003 20:14:15 -0800 User-Agent: KMail/1.4.3 References: <3B663B41B350D311AEEF00A0C9254563201A70@VISION1> In-Reply-To: <3B663B41B350D311AEEF00A0C9254563201A70@VISION1> MIME-Version: 1.0 Message-Id: <200302132014.15805.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/169 X-Sequence-Number: 6185 Micheal, > How do you get an array of elements to be passed to a stored proc such th= at > 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= =20 variable. 2) Build up your query as a dynamic string. 3) Do your query loop as a FOR record IN EXECUTE dynamic_query_string --=20 Josh Berkus Aglio Database Solutions San Francisco From sszabo@megazone23.bigpanda.com Fri Feb 14 04:57:28 2003 X-Original-To: pgsql-novice@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E6C50474E4F; Thu, 13 Feb 2003 23:57:28 -0500 (EST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 5BD0D474E42; Thu, 13 Feb 2003 23:57:28 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 6293ED6AD; Thu, 13 Feb 2003 20:57:33 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 583625C0F; Thu, 13 Feb 2003 20:57:33 -0800 (PST) Date: Thu, 13 Feb 2003 20:57:33 -0800 (PST) From: Stephan Szabo To: Michael Weaver Cc: "'pgsql-novice@postgresql.org'" , "'pgsql-sql@postgresql.org'" Subject: Re: [SQL] Passing arrays In-Reply-To: <3B663B41B350D311AEEF00A0C9254563201A70@VISION1> Message-ID: <20030213205623.V56568-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/180 X-Sequence-Number: 6196 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. From mweaver@corpusglobe.com Fri Feb 14 07:54:58 2003 X-Original-To: pgsql-novice@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5F330474E4F; Fri, 14 Feb 2003 02:54:58 -0500 (EST) Received: from vision1.touristvision.com.au (vision1.corpusglobe.com [202.61.128.2]) by postgresql.org (Postfix) with ESMTP id 8B2B3474E42; Fri, 14 Feb 2003 02:54:56 -0500 (EST) Received: by VISION1 with Internet Mail Service (5.5.2653.19) id ; Fri, 14 Feb 2003 16:10:01 +0800 Message-ID: <3B663B41B350D311AEEF00A0C9254563201A72@VISION1> From: Michael Weaver To: 'Stephan Szabo' , Michael Weaver Cc: "'pgsql-novice@postgresql.org'" , "'pgsql-sql@postgresql.org'" Subject: Re: [SQL] Passing arrays Date: Fri, 14 Feb 2003 16:09:55 +0800 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2D400.75B77100" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/170 X-Sequence-Number: 6186 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_01C2D400.75B77100 Content-Type: text/plain; charset="iso-8859-1" > -----Original Message----- > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] > Sent: Friday, 14 February 2003 12:58 PM > To: Michael Weaver > Cc: 'pgsql-novice@postgresql.org'; 'pgsql-sql@postgresql.org' > Subject: Re: [SQL] Passing arrays > > > > 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. I've got around this limitation using strings, but I do find it suprising, that Postgres handles arrays this way. Searching an array in an WHERE IN clause would be a useful task. (namely for passing arrays of values between stored functions) Ok it could be confusing, but I think a number of other DBs employ it to good effect. I'll take a look at the array extension in contrib - it sounds useful. Thanks. ------_=_NextPart_001_01C2D400.75B77100 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [SQL] Passing arrays

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: Friday, 14 February 2003 12:58 PM
> To: Michael Weaver
> Cc: 'pgsql-novice@postgresql.org'; 'pgsql-sql@postg= resql.org'
> Subject: Re: [SQL] Passing arrays
>
>
>
> On Fri, 14 Feb 2003, Michael Weaver wrote:
>
> > How do you get an array of elements to be pass= ed 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.&nbs= p; That's attempting to
> compare the field directly to the array as a whole = (which is
> why you get
> the particular error you do.

I've got around this limitation using strings, but I do f= ind it suprising, that Postgres handles arrays this way.
Searching an array in an WHERE IN clause would be a usef= ul task. (namely for passing arrays of values between stored functions)

Ok it could be confusing, but I think a number of other D= Bs employ it to good effect.

I'll take a look at the array extension in contrib - it s= ounds useful.

Thanks.

------_=_NextPart_001_01C2D400.75B77100-- From ddurst@larubber.com Fri Feb 14 08:20:52 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 16462475C8B for ; Fri, 14 Feb 2003 03:20:52 -0500 (EST) Received: from localhost.localdomain (unknown [216.31.142.3]) by postgresql.org (Postfix) with ESMTP id 7C12C475C45 for ; Fri, 14 Feb 2003 03:20:49 -0500 (EST) Received: from larubber.com (server [127.0.0.1]) by localhost.localdomain (8.11.6/8.11.6) with SMTP id h1E8vxP05953 for ; Fri, 14 Feb 2003 00:57:59 -0800 Received: from 216.86.192.34 (SquirrelMail authenticated user ddurst) by www.la-rubber.com with HTTP; Fri, 14 Feb 2003 00:57:59 -0800 (PST) Message-ID: <42097.216.86.192.34.1045213079.squirrel@www.la-rubber.com> Date: Fri, 14 Feb 2003 00:57:59 -0800 (PST) Subject: Re: Passing arrays From: "David Durst" To: In-Reply-To: References: <3B663B41B350D311AEEF00A0C9254563201A72@VISION1> X-Priority: 3 Importance: Normal X-MSMail-Priority: Normal Reply-To: ddurst@larubber.com X-Mailer: SquirrelMail (version 1.2.7) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/322 X-Sequence-Number: 11720 This is slightly off the original topic but related. Is there any function or way I can easily find the size of the passed array? > On Fri, 14 Feb 2003, Michael Weaver wrote: > >> >> >> > -----Original Message----- >> > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] >> > Sent: Friday, 14 February 2003 12:58 PM >> > To: Michael Weaver >> > Cc: 'pgsql-novice@postgresql.org'; 'pgsql-sql@postgresql.org' >> > Subject: Re: [SQL] Passing arrays >> > >> > >> > >> > 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. >> >> I've got around this limitation using strings, but I do find it >> suprising, that Postgres handles arrays this way. >> Searching an array in an WHERE IN clause would be a useful task. >> (namely for passing arrays of values between stored functions) >> >> Ok it could be confusing, but I think a number of other DBs employ it >> to good effect. >> >> I'll take a look at the array extension in contrib - it sounds useful. > > Also check out contrib/intarray and its indexing capabilites > if you have 1 dimensional integer arrays. > >> >> Thanks. >> > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > Achilleus Mantzios > S/W Engineer > IT dept > Dynacom Tankers Mngmt > Nikis 4, Glyfada > Athens 16610 > Greece > tel: +30-10-8981112 > fax: +30-10-8981877 > email: achill@matrix.gatewaynet.com > mantzios@softlab.ece.ntua.gr > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org From mweaver@corpusglobe.com Fri Feb 14 09:14:40 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4964B475B8E for ; Fri, 14 Feb 2003 04:14:40 -0500 (EST) Received: from vision1.touristvision.com.au (vision1.corpusglobe.com [202.61.128.2]) by postgresql.org (Postfix) with ESMTP id BA029475B8A for ; Fri, 14 Feb 2003 04:14:36 -0500 (EST) Received: by VISION1 with Internet Mail Service (5.5.2653.19) id ; Fri, 14 Feb 2003 17:29:42 +0800 Message-ID: <3B663B41B350D311AEEF00A0C9254563201A73@VISION1> From: Michael Weaver To: "'ddurst@larubber.com'" , pgsql-sql@postgresql.org Subject: Re: Passing arrays Date: Fri, 14 Feb 2003 17:29:36 +0800 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2D40B.975E3EA0" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/324 X-Sequence-Number: 11722 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_01C2D40B.975E3EA0 Content-Type: text/plain; charset="iso-8859-1" There is a function array_dims(array) that returns the size of array. It's not THAT useful as it returns a string like '[1:2]' <-( 1 lower, 2 upper bound.) With a little bit of string processing you could get the size of the array. > -----Original Message----- > From: David Durst [mailto:ddurst@larubber.com] > Sent: Friday, 14 February 2003 4:58 PM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] Passing arrays > > > This is slightly off the original topic but related. > > Is there any function or way I can easily find the size of > the passed array? > > On Fri, 14 Feb 2003, Michael Weaver wrote: > > > >> > >> > >> > -----Original Message----- > >> > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] > >> > Sent: Friday, 14 February 2003 12:58 PM > >> > To: Michael Weaver > >> > Cc: 'pgsql-novice@postgresql.org'; 'pgsql-sql@postgresql.org' > >> > Subject: Re: [SQL] Passing arrays > >> > > >> > > >> > > >> > 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. > >> > >> I've got around this limitation using strings, but I do find it > >> suprising, that Postgres handles arrays this way. > >> Searching an array in an WHERE IN clause would be a useful task. > >> (namely for passing arrays of values between stored functions) > >> > >> Ok it could be confusing, but I think a number of other > DBs employ it > >> to good effect. > >> > >> I'll take a look at the array extension in contrib - it > sounds useful. > > > > Also check out contrib/intarray and its indexing capabilites > > if you have 1 dimensional integer arrays. > > > >> > >> Thanks. > >> > > > > ================================================================== > > Achilleus Mantzios > > S/W Engineer > > IT dept > > Dynacom Tankers Mngmt > > Nikis 4, Glyfada > > Athens 16610 > > Greece > > tel: +30-10-8981112 > > fax: +30-10-8981877 > > email: achill@matrix.gatewaynet.com > > mantzios@softlab.ece.ntua.gr > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) > ------_=_NextPart_001_01C2D40B.975E3EA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [SQL] Passing arrays

There is a function array_dims(array) that returns the si= ze of array.
It's not THAT useful as it returns a string like '[1:2]'= <-( 1 lower, 2 upper bound.)
With a little bit of string processing you could get the= size of the array.

> -----Original Message-----
> From: David Durst [mailto:ddurst@larubber.com]
> Sent: Friday, 14 February 2003 4:58 PM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Passing arrays
>
>
> This is slightly off the original topic but related= .
>
> Is there any function or way I can easily find the = size of
> the passed array?
> > On Fri, 14 Feb 2003, Michael Weaver wrote:
> >
> >>
> >>
> >> > -----Original Message-----
> >> > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com= ]
> >> > Sent: Friday, 14 February 2003 12:58 = PM
> >> > To: Michael Weaver
> >> > Cc: 'pgsql-novice@postgresql.org'; 'p= gsql-sql@postgresql.org'
> >> > Subject: Re: [SQL] Passing arrays
> >> >
> >> >
> >> >
> >> > On Fri, 14 Feb 2003, Michael Weaver w= rote:
> >> >
> >> > > How do you get an array of eleme= nts to be passed to a
> >> > stored proc such that
> >> > > you can use the array in a SELEC= T statement in the WHERE clause
> >> > > - e.g. WHERE field I= N (array) etc...
> >> >
> >> > A straight WHERE field IN (array) doe= sn'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.
> >>
> >> I've got around this limitation using stri= ngs, but I do find it
> >> suprising, that Postgres handles arrays th= is way.
> >> Searching an array in an WHERE IN clause w= ould be a useful task.
> >> (namely for passing arrays of values betwe= en stored functions)
> >>
> >> Ok it could be confusing, but I think a nu= mber of other
> DBs employ it
> >> to good effect.
> >>
> >> I'll take a look at the array extension in= contrib - it
> sounds useful.
> >
> > Also check out contrib/intarray and its indexi= ng capabilites
> > if you have 1 dimensional integer arrays.
> >
> >>
> >> Thanks.
> >>
> >
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D
> > Achilleus Mantzios
> > S/W Engineer
> > IT dept
> > Dynacom Tankers Mngmt
> > Nikis 4, Glyfada
> > Athens 16610
> > Greece
> > tel:    +30-10-8981112
> > fax:    +30-10-8981877
> > email: achill@matrix.gatewaynet.com
> >        = ; mantzios@softlab.ece.ntua.gr
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go t= o
> majordomo@postgresql.org
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the u= nregister command
>     (send "unregister Your= EmailAddressHere" to
> majordomo@postgresql.org)
>

------_=_NextPart_001_01C2D40B.975E3EA0-- From achill@matrix.gatewaynet.com Fri Feb 14 08:08:56 2003 X-Original-To: pgsql-novice@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 76C04474E4F; Fri, 14 Feb 2003 03:08:56 -0500 (EST) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 23BFF474E42; Fri, 14 Feb 2003 03:08:55 -0500 (EST) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50] (may be forged)) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h1ECABIs021021; Fri, 14 Feb 2003 10:10:11 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h1ECA936021017; Fri, 14 Feb 2003 10:10:10 -0200 Date: Fri, 14 Feb 2003 10:10:09 -0200 (GMT+2) From: Achilleus Mantzios To: Michael Weaver Cc: "'Stephan Szabo'" , "'pgsql-novice@postgresql.org'" , "'pgsql-sql@postgresql.org'" Subject: Re: [SQL] Passing arrays In-Reply-To: <3B663B41B350D311AEEF00A0C9254563201A72@VISION1> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/179 X-Sequence-Number: 6195 On Fri, 14 Feb 2003, Michael Weaver wrote: > > > > -----Original Message----- > > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] > > Sent: Friday, 14 February 2003 12:58 PM > > To: Michael Weaver > > Cc: 'pgsql-novice@postgresql.org'; 'pgsql-sql@postgresql.org' > > Subject: Re: [SQL] Passing arrays > > > > > > > > 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. > > I've got around this limitation using strings, but I do find it suprising, > that Postgres handles arrays this way. > Searching an array in an WHERE IN clause would be a useful task. (namely for > passing arrays of values between stored functions) > > Ok it could be confusing, but I think a number of other DBs employ it to > good effect. > > I'll take a look at the array extension in contrib - it sounds useful. Also check out contrib/intarray and its indexing capabilites if you have 1 dimensional integer arrays. > > Thanks. > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr From achill@matrix.gatewaynet.com Fri Feb 14 09:16:02 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0953C475D00 for ; Fri, 14 Feb 2003 04:16:02 -0500 (EST) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id ED300475C2B for ; Fri, 14 Feb 2003 04:15:56 -0500 (EST) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50] (may be forged)) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h1EDHVIs021367; Fri, 14 Feb 2003 11:17:31 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h1EDHVTv021363; Fri, 14 Feb 2003 11:17:31 -0200 Date: Fri, 14 Feb 2003 11:17:31 -0200 (GMT+2) From: Achilleus Mantzios To: David Durst Cc: pgsql-sql@postgresql.org Subject: Re: Passing arrays In-Reply-To: <42097.216.86.192.34.1045213079.squirrel@www.la-rubber.com> Message-ID: MIME-Version: 1.0 Content-Type: MULTIPART/MIXED; BOUNDARY="8323328-2067803833-1045228651=:21195" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/325 X-Sequence-Number: 11723 This message is in MIME format. The first part should be readable text, while the remaining parts are likely unreadable without MIME-aware tools. Send mail to mime@docserver.cac.washington.edu for more info. --8323328-2067803833-1045228651=:21195 Content-Type: TEXT/PLAIN; charset=US-ASCII On Fri, 14 Feb 2003, David Durst wrote: > This is slightly off the original topic but related. > > Is there any function or way I can easily find the size of the passed array? You can always parse SELECT array_dims()::text from ; Or write a function using the macros in /usr/local/pgsql/include/server/utils/array.h to get the size of each dimension. (i have attached a simple function that does that) > > On Fri, 14 Feb 2003, Michael Weaver wrote: > > > >> > >> > >> > -----Original Message----- > >> > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] > >> > Sent: Friday, 14 February 2003 12:58 PM > >> > To: Michael Weaver > >> > Cc: 'pgsql-novice@postgresql.org'; 'pgsql-sql@postgresql.org' > >> > Subject: Re: [SQL] Passing arrays > >> > > >> > > >> > > >> > 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. > >> > >> I've got around this limitation using strings, but I do find it > >> suprising, that Postgres handles arrays this way. > >> Searching an array in an WHERE IN clause would be a useful task. > >> (namely for passing arrays of values between stored functions) > >> > >> Ok it could be confusing, but I think a number of other DBs employ it > >> to good effect. > >> > >> I'll take a look at the array extension in contrib - it sounds useful. > > > > Also check out contrib/intarray and its indexing capabilites > > if you have 1 dimensional integer arrays. > > > >> > >> Thanks. > >> > > > > ================================================================== > > Achilleus Mantzios > > S/W Engineer > > IT dept > > Dynacom Tankers Mngmt > > Nikis 4, Glyfada > > Athens 16610 > > Greece > > tel: +30-10-8981112 > > fax: +30-10-8981877 > > email: achill@matrix.gatewaynet.com > > mantzios@softlab.ece.ntua.gr > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr --8323328-2067803833-1045228651=:21195 Content-Type: TEXT/PLAIN; charset=US-ASCII; name="arrlevel.c" Content-Transfer-Encoding: BASE64 Content-ID: Content-Description: Content-Disposition: attachment; filename="arrlevel.c" I2luY2x1ZGUgInBvc3RncmVzLmgiDQogDQojaW5jbHVkZSAiYWNjZXNzL2dp c3QuaCINCiNpbmNsdWRlICJhY2Nlc3MvaXR1cC5oIg0KI2luY2x1ZGUgImFj Y2Vzcy9ydHJlZS5oIg0KI2luY2x1ZGUgInV0aWxzL2Vsb2cuaCINCiNpbmNs dWRlICJ1dGlscy9wYWxsb2MuaCINCiNpbmNsdWRlICJ1dGlscy9hcnJheS5o Ig0KI2luY2x1ZGUgInV0aWxzL2J1aWx0aW5zLmgiDQojaW5jbHVkZSAic3Rv cmFnZS9idWZwYWdlLmgiDQoNClBHX0ZVTkNUSU9OX0lORk9fVjEobGV2ZWwp Ow0KRGF0dW0gbGV2ZWwoUEdfRlVOQ1RJT05fQVJHUyk7IA0KDQpEYXR1bSBs ZXZlbChQR19GVU5DVElPTl9BUkdTKQ0Kew0KCWludCBuaXRlbXMsbmRpbSwq ZGltOw0KCWNoYXIgKnA9MDsNCglBcnJheVR5cGUgKmludGFycmF5ID0gKEFy cmF5VHlwZSAqKSBEYXR1bUdldFBvaW50ZXIoUEdfR0VUQVJHX0RBVFVNKDAp KTsNCglpZiAgKGludGFycmF5ID09IDAgKSByZXR1cm4gKGludDQpIDA7DQoJ aW50YXJyYXkgPSBEYXR1bUdldEFycmF5VHlwZVAoUG9pbnRlckdldERhdHVt KGludGFycmF5KSk7DQoJaWYgIChpbnRhcnJheSA9PSAwICkgcmV0dXJuIChp bnQ0KSAwOw0KCXAgPSBBUlJfREFUQV9QVFIoaW50YXJyYXkpOw0KCWlmIChw ID09IDApIHJldHVybiAoaW50NCkgMDsNCgluZGltID0gQVJSX05ESU0oaW50 YXJyYXkpOw0KCWRpbSA9IEFSUl9ESU1TKGludGFycmF5KTsNCgluaXRlbXMg PSBBcnJheUdldE5JdGVtcyhuZGltLCBkaW0pOw0KCXJldHVybiAoaW50NCkg bml0ZW1zOw0KfQ0K --8323328-2067803833-1045228651=:21195-- From mail@joeconway.com Fri Feb 14 15:50:13 2003 X-Original-To: pgsql-novice@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3F9C1475A45; Fri, 14 Feb 2003 10:50:13 -0500 (EST) Received: from joeconway.com (unknown [63.210.180.150]) by postgresql.org (Postfix) with ESMTP id 7840D4759AF; Fri, 14 Feb 2003 10:50:12 -0500 (EST) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.0.4) with ESMTP-TLS id 1611842; Fri, 14 Feb 2003 08:25:19 -0800 Message-ID: <3E4D0FC4.7080103@joeconway.com> Date: Fri, 14 Feb 2003 07:48:20 -0800 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Achilleus Mantzios Cc: Michael Weaver , 'Stephan Szabo' , "'pgsql-novice@postgresql.org'" , "'pgsql-sql@postgresql.org'" Subject: Re: [SQL] Passing arrays References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/177 X-Sequence-Number: 6193 Achilleus Mantzios wrote: > Also check out contrib/intarray and its indexing capabilites > if you have 1 dimensional integer arrays. > For alternative array processing capabilities, you might want to look at PL/R (see: http://www.joeconway.com/plr/ ). R is pretty much completely array/vector based. I've not made an official "release" announcement, but I am interested in testers/feedback. HTH, Joe From guy@incentre.net Wed Feb 19 19:06:57 2003 X-Original-To: pgsql-sql@postgresql.org Received: from bach.ccinet.ab.ca (bach.ccinet.ab.ca [198.161.96.1]) by postgresql.org (Postfix) with ESMTP id 1BCC7475AEC for ; Wed, 19 Feb 2003 14:06:57 -0500 (EST) Received: from incentre.net (squid.incentre.net [206.75.213.195]) by bach.ccinet.ab.ca (8.12.6/8.12.6) with ESMTP id h1JJ7BGo065147 for ; Wed, 19 Feb 2003 12:07:11 -0700 (MST) (envelope-from guy@incentre.net) Message-ID: <3E53D5D0.7020104@incentre.net> Date: Wed, 19 Feb 2003 12:06:56 -0700 From: Guy Fraser Organization: J I Reasearch User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20021003 X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Passing arrays References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200302/432 X-Sequence-Number: 11830 Can someone explain to me why people keep refering to contrib/intarray. This person need help with a text array not an int array. From what I can tell people keep refering to some GIST cruft but have never supplied an example. If I am missing something please let me know, I am sure all members of this list would appreciate more details on the issues of arrays. My wish list: array_insert(array_column _std_array_types_[],element_insertion_point int) returns _std_array_types_[] array_replace(array_column _std_array_types_[],element_to_replace int) returns std_array_types_[] array_remove(array_column _std_array_types_[],element_to_remove int) returns std_array_types_[] array_append(array_column _std_array_types_[]) returns std_array_types_[] array_accumulate(data_to_append _std_type_) returns _array_of_same_type_[] array_size(array_column _std_array_types_[]) returns int I have made my own array_size functions, but they are on one my machines at home. I have been tring to build an agrigate "array_accumulate" for text data but have not had success. Using my array_size function I tried to just use an update but it would not work, this is basicly what I tried. update table2 set data_array[array_size(data_array) + 1] = table1.new_data where table2.id_data = table1.match_data; The data in table1 was a list from the standard alias file as (alias text,dest text) and the data in table2 was a distinct list of destinations from list1 and an array of '{nobody}' like this (dest text not null,aliases text[] default '{nobody}'). When I run the update query only the first match is added to the array. The other weirdness was, that I couldn't seen to add the first element of the array. That may have been fixed though when I changed my function to remove the "strict immutable" attributes. I have not had any success trying to build a function to do the update so I can use it in an aggrigate. To make the functions type insensitive I have been thinking it may be possible to cast the array or array data to text on input and back to the original type on output, but I would rather find a better way to do it. Guy Achilleus Mantzios wrote: > On Fri, 14 Feb 2003, Michael Weaver wrote: ..snip... >>>-----Original Message----- >>>From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] >>>Sent: Friday, 14 February 2003 12:58 PM >>>To: Michael Weaver >>>Cc: 'pgsql-novice@postgresql.org'; 'pgsql-sql@postgresql.org' >>>Subject: Re: [SQL] Passing arrays ...snip... > > Also check out contrib/intarray and its indexing capabilites > if you have 1 dimensional integer arrays. > > >>Thanks. >> > ...snip... From guy@incentre.net Wed Feb 19 20:41:51 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 47A0D475EBF for ; Wed, 19 Feb 2003 15:41:51 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 48090-07 for ; Wed, 19 Feb 2003 15:41:40 -0500 (EST) Received: from bach.ccinet.ab.ca (bach.ccinet.ab.ca [198.161.96.1]) by postgresql.org (Postfix) with ESMTP id 1F352475E2C for ; Wed, 19 Feb 2003 15:26:58 -0500 (EST) Received: from incentre.net (squid.incentre.net [206.75.213.195]) by bach.ccinet.ab.ca (8.12.6/8.12.6) with ESMTP id h1JKRDGo090697 for ; Wed, 19 Feb 2003 13:27:13 -0700 (MST) (envelope-from guy@incentre.net) Message-ID: <3E53E892.6050805@incentre.net> Date: Wed, 19 Feb 2003 13:26:58 -0700 From: Guy Fraser Organization: J I Reasearch User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20021003 X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Passing arrays References: <3B663B41B350D311AEEF00A0C9254563201A73@VISION1> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/437 X-Sequence-Number: 11835 Would it not be more reasonable to have array_dims return an int or int[]? Has anyone ever seen an array that does not start at 1? The other problem I find with array_dims returning text is when you have a multi-dimentional array like this IIRC; array_dims('{{asd,fgh,jkl},{zxc,vbn,mlk}}') = '[1:2][1:3]' Which appears to mean that there the data is a 2 element array of a 3 element array. If the data was in an int array format like '{{1,2},{1,3}}' it would be dead easy to get the dimentions of the array without messy text parsing. It would be even better as '{2,3}' since a null element at the start of array is still counted as an element so all arrays start from 1. A fairly simple function could be made to "factor" all dimentions together to get a full sub_element count, ie. 2x3 = 6 ... . I think I will update my array_size function to handle this, but that means my funtion has to deal with more messy text parsing to generate the int array for multi dimentional arrays. I have up until now only been working with single element arrays. Here is an example of my array_size function for text arrays, I just tossed this together from what I could remember, so it may not be exactly the same as what I am using. For V7.3 it should look somthing like this. ---%<...snip... CREATE FUNCTION array_size(text[]) RETURNS int AS ' DECLARE array ALIAS FOR $1; dim int; BEGIN SELECT INTO dim replace(split_part(array_dims(array),'':'',2),'']'','''')::int ; -- that was the messy stuff IF dim IS NULL THEN dim := 0 ; END IF; RETURN dim; END; ' LANGUAGE 'plpgsql'; ---%<...snip... For V7.2 it looked something like this, but it is more messy. ---%<...snip... CREATE FUNCTION array_size(text[]) RETURNS int AS ' DECLARE array ALIAS FOR $1; dim int; BEGIN SELECT INTO dim rtrim(ltrim(ltrim(array_dims($1),''[012345679''),'':''),'']'')::int ; -- that was the messy stuff IF dim IS NULL THEN dim := 0 ; END IF; RETURN dim; END; ' LANGUAGE 'plpgsql'; ---%<...snip... I dropped these into a test DB, created test table and they do work so, here are the results: select *,array_size(destination) from size_test; alias | destination | array_size -----------+---------------------------+------------ alias1 | {dest1} | 1 alias2 | {dest2,dest1} | 2 alias3 | {dest3,dest4} | 2 alias4 | {dest3,dest4,dest5} | 3 alias5 | {dest6,dest7} | 2 alias6 | {dest3,dest7,dest4,dest5} | 4 alias7 | | 0 I hope that this helps. You can over load the function by creating more of the same function but using different array types for the input. IE. array_size(int[]) instead of array_size(text[]). Guy Michael Weaver wrote: > There is a function array_dims(array) that returns the size of array. > It's not THAT useful as it returns a string like '[1:2]' <-( 1 lower, 2 > upper bound.) > With a little bit of string processing you could get the size of the array. > > >