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