Thread: Passing arrays

Passing arrays

From
Michael Weaver
Date:

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.

Re: Passing arrays

From
Josh Berkus
Date:
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

Re: Passing arrays

From
Stephan Szabo
Date:
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.


Re: Passing arrays

From
Michael Weaver
Date:


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

Re: Passing arrays

From
Achilleus Mantzios
Date:
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


Re: Passing arrays

From
"David Durst"
Date:
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





Re: Passing arrays

From
Michael Weaver
Date:
<p><font size="2">There is a function array_dims(array) that returns the size of array.</font><br /><font size="2">It's
notTHAT useful as it returns a string like '[1:2]' <-( 1 lower, 2 upper bound.)</font><br /><font size="2">With a
littlebit of string processing you could get the size of the array.</font><br /><br /><p><font size="2">>
-----OriginalMessage-----</font><br /><font size="2">> From: David Durst [<a
href="mailto:ddurst@larubber.com">mailto:ddurst@larubber.com</a>]</font><br/><font size="2">> Sent: Friday, 14
February2003 4:58 PM</font><br /><font size="2">> To: pgsql-sql@postgresql.org</font><br /><font size="2">>
Subject:Re: [SQL] Passing arrays</font><br /><font size="2">> </font><br /><font size="2">> </font><br /><font
size="2">>This is slightly off the original topic but related.</font><br /><font size="2">> </font><br /><font
size="2">>Is there any function or way I can easily find the size of </font><br /><font size="2">> the passed
array?</font><br/><font size="2">> > On Fri, 14 Feb 2003, Michael Weaver wrote:</font><br /><font size="2">>
></font><br/><font size="2">> >></font><br /><font size="2">> >></font><br /><font size="2">>
>>> -----Original Message-----</font><br /><font size="2">> >> > From: Stephan Szabo [<a
href="mailto:sszabo@megazone23.bigpanda.com">mailto:sszabo@megazone23.bigpanda.com</a>]</font><br/><font size="2">>
>>> Sent: Friday, 14 February 2003 12:58 PM</font><br /><font size="2">> >> > To: Michael
Weaver</font><br/><font size="2">> >> > Cc: 'pgsql-novice@postgresql.org';
'pgsql-sql@postgresql.org'</font><br/><font size="2">> >> > Subject: Re: [SQL] Passing arrays</font><br
/><fontsize="2">> >> ></font><br /><font size="2">> >> ></font><br /><font size="2">>
>>></font><br /><font size="2">> >> > On Fri, 14 Feb 2003, Michael Weaver wrote:</font><br /><font
size="2">>>> ></font><br /><font size="2">> >> > > How do you get an array of elements to be
passedto a</font><br /><font size="2">> >> > stored proc such that</font><br /><font size="2">> >>
>> you can use the array in a SELECT statement in the WHERE clause</font><br /><font size="2">> >> >
> - e.g.  WHERE field IN (array) etc...</font><br /><font size="2">> >> ></font><br /><font
size="2">>>> > A straight WHERE field IN (array) doesn't work.  That's </font><br /><font size="2">>
attempting</font><br/><font size="2">> >> to compare the field directly to the array as a whole (which
is</font><br/><font size="2">> >> > why you get</font><br /><font size="2">> >> > the
particularerror you do.</font><br /><font size="2">> >></font><br /><font size="2">> >> I've got
aroundthis limitation using strings, but I do find it</font><br /><font size="2">> >> suprising, that Postgres
handlesarrays this way.</font><br /><font size="2">> >> Searching an array in an WHERE IN clause would be a
usefultask.</font><br /><font size="2">> >> (namely for passing arrays of values between stored
functions)</font><br/><font size="2">> >></font><br /><font size="2">> >> Ok it could be confusing,
butI think a number of other </font><br /><font size="2">> DBs employ it</font><br /><font size="2">> >> to
goodeffect.</font><br /><font size="2">> >></font><br /><font size="2">> >> I'll take a look at the
arrayextension in contrib - it </font><br /><font size="2">> sounds useful.</font><br /><font size="2">>
></font><br/><font size="2">> > Also check out contrib/intarray and its indexing capabilites</font><br /><font
size="2">>> if you have 1 dimensional integer arrays.</font><br /><font size="2">> ></font><br /><font
size="2">>>></font><br /><font size="2">> >> Thanks.</font><br /><font size="2">>
>></font><br/><font size="2">> ></font><br /><font size="2">> >
==================================================================</font><br/><font size="2">> > Achilleus
Mantzios</font><br/><font size="2">> > S/W Engineer</font><br /><font size="2">> > IT dept</font><br
/><fontsize="2">> > Dynacom Tankers Mngmt</font><br /><font size="2">> > Nikis 4, Glyfada</font><br /><font
size="2">>> Athens 16610</font><br /><font size="2">> > Greece</font><br /><font size="2">> > tel:   
+30-10-8981112</font><br/><font size="2">> > fax:    +30-10-8981877</font><br /><font size="2">> > email: 
achill@matrix.gatewaynet.com</font><br/><font size="2">> >         mantzios@softlab.ece.ntua.gr</font><br /><font
size="2">>></font><br /><font size="2">> ></font><br /><font size="2">> >
---------------------------(endof </font><br /><font size="2">> broadcast)---------------------------</font><br
/><fontsize="2">> > TIP 1: subscribe and unsubscribe commands go to </font><br /><font size="2">>
majordomo@postgresql.org</font><br/><font size="2">> </font><br /><font size="2">> </font><br /><font
size="2">></font><br /><font size="2">> </font><br /><font size="2">> ---------------------------(end of
</font><br/><font size="2">> broadcast)---------------------------</font><br /><font size="2">> TIP 2: you can
getoff all lists at once with the unregister command</font><br /><font size="2">>     (send "unregister
YourEmailAddressHere"to </font><br /><font size="2">> majordomo@postgresql.org)</font><br /><font size="2">>
</font>

Re: Passing arrays

From
Achilleus Mantzios
Date:
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(<your array column here>)::text from <your table here> ;

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

Re: Passing arrays

From
Joe Conway
Date:
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


Re: Passing arrays

From
Guy Fraser
Date:
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
200312:58 PM>>>To: Michael Weaver>>>Cc: 'pgsql-novice@postgresql.org'; 'pgsql-sql@postgresql.org'>>>Subject: Re: [SQL]
Passingarrays
 
...snip...
>> Also check out contrib/intarray and its indexing capabilites> if you have 1 dimensional integer
arrays.>>>>Thanks.>>>
...snip...




Re: Passing arrays

From
Guy Fraser
Date:
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.
> 
> 
>