pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPassing arrays
11+ messages / 7 participants
[nested] [flat]
* Passing arrays
@ 2003-02-14 04:09 Michael Weaver <mweaver@corpusglobe.com>
2003-02-14 04:14 ` Re: [SQL] Passing arrays Josh Berkus <josh@agliodbs.com>
2003-02-14 04:57 ` Re: [SQL] Passing arrays Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 11+ messages in thread
From: Michael Weaver @ 2003-02-14 04:09 UTC (permalink / raw)
To: 'pgsql-novice@postgresql.org' <pgsql-novice@postgresql.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Passing arrays
2003-02-14 04:09 Passing arrays Michael Weaver <mweaver@corpusglobe.com>
@ 2003-02-14 04:14 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2003-02-14 04:14 UTC (permalink / raw)
To: Michael Weaver <mweaver@corpusglobe.com>; 'pgsql-novice@postgresql.org' <pgsql-novice@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Passing arrays
2003-02-14 04:09 Passing arrays Michael Weaver <mweaver@corpusglobe.com>
@ 2003-02-14 04:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2003-02-14 04:57 UTC (permalink / raw)
To: Michael Weaver <mweaver@corpusglobe.com>; +Cc: 'pgsql-novice@postgresql.org' <pgsql-novice@postgresql.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Passing arrays
@ 2003-02-14 08:09 Michael Weaver <mweaver@corpusglobe.com>
2003-02-14 12:10 ` Re: [SQL] Passing arrays Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Weaver @ 2003-02-14 08:09 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Michael Weaver <mweaver@corpusglobe.com>; +Cc: 'pgsql-novice@postgresql.org' <pgsql-novice@postgresql.org>; pgsql-sql
> -----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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Passing arrays
2003-02-14 08:09 Re: [SQL] Passing arrays Michael Weaver <mweaver@corpusglobe.com>
@ 2003-02-14 12:10 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-02-14 08:57 ` Re: Passing arrays David Durst <ddurst@larubber.com>
2003-02-14 15:48 ` Re: [SQL] Passing arrays Joe Conway <mail@joeconway.com>
2003-02-19 19:06 ` Re: Passing arrays Guy Fraser <guy@incentre.net>
0 siblings, 3 replies; 11+ messages in thread
From: Achilleus Mantzios @ 2003-02-14 12:10 UTC (permalink / raw)
To: Michael Weaver <mweaver@corpusglobe.com>; +Cc: 'Stephan Szabo' <sszabo@megazone23.bigpanda.com>; 'pgsql-novice@postgresql.org' <pgsql-novice@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Passing arrays
2003-02-14 08:09 Re: [SQL] Passing arrays Michael Weaver <mweaver@corpusglobe.com>
2003-02-14 12:10 ` Re: [SQL] Passing arrays Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2003-02-14 08:57 ` David Durst <ddurst@larubber.com>
2003-02-14 13:17 ` Re: Passing arrays Achilleus Mantzios <achill@matrix.gatewaynet.com>
2 siblings, 1 reply; 11+ messages in thread
From: David Durst @ 2003-02-14 08:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Passing arrays
2003-02-14 08:09 Re: [SQL] Passing arrays Michael Weaver <mweaver@corpusglobe.com>
2003-02-14 12:10 ` Re: [SQL] Passing arrays Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-02-14 08:57 ` Re: Passing arrays David Durst <ddurst@larubber.com>
@ 2003-02-14 13:17 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 11+ messages in thread
From: Achilleus Mantzios @ 2003-02-14 13:17 UTC (permalink / raw)
To: David Durst <ddurst@larubber.com>; +Cc: pgsql-sql
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
Attachments:
arrlevel.c (text/plain, 777B) download
#include "postgres.h"
#include "access/gist.h"
#include "access/itup.h"
#include "access/rtree.h"
#include "utils/elog.h"
#include "utils/palloc.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "storage/bufpage.h"
PG_FUNCTION_INFO_V1(level);
Datum level(PG_FUNCTION_ARGS);
Datum level(PG_FUNCTION_ARGS)
{
int nitems,ndim,*dim;
char *p=0;
ArrayType *intarray = (ArrayType *) DatumGetPointer(PG_GETARG_DATUM(0));
if (intarray == 0 ) return (int4) 0;
intarray = DatumGetArrayTypeP(PointerGetDatum(intarray));
if (intarray == 0 ) return (int4) 0;
p = ARR_DATA_PTR(intarray);
if (p == 0) return (int4) 0;
ndim = ARR_NDIM(intarray);
dim = ARR_DIMS(intarray);
nitems = ArrayGetNItems(ndim, dim);
return (int4) nitems;
}
* Re: [SQL] Passing arrays
2003-02-14 08:09 Re: [SQL] Passing arrays Michael Weaver <mweaver@corpusglobe.com>
2003-02-14 12:10 ` Re: [SQL] Passing arrays Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2003-02-14 15:48 ` Joe Conway <mail@joeconway.com>
2 siblings, 0 replies; 11+ messages in thread
From: Joe Conway @ 2003-02-14 15:48 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Michael Weaver <mweaver@corpusglobe.com>; Stephan Szabo <sszabo@megazone23.bigpanda.com>; 'pgsql-novice@postgresql.org' <pgsql-novice@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Passing arrays
2003-02-14 08:09 Re: [SQL] Passing arrays Michael Weaver <mweaver@corpusglobe.com>
2003-02-14 12:10 ` Re: [SQL] Passing arrays Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2003-02-19 19:06 ` Guy Fraser <guy@incentre.net>
2 siblings, 0 replies; 11+ messages in thread
From: Guy Fraser @ 2003-02-19 19:06 UTC (permalink / raw)
To: pgsql-sql
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...
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Passing arrays
@ 2003-02-14 09:29 Michael Weaver <mweaver@corpusglobe.com>
2003-02-19 20:26 ` Re: Passing arrays Guy Fraser <guy@incentre.net>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Weaver @ 2003-02-14 09:29 UTC (permalink / raw)
To: 'ddurst@larubber.com' <ddurst@larubber.com>; pgsql-sql
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)
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Passing arrays
2003-02-14 09:29 Re: Passing arrays Michael Weaver <mweaver@corpusglobe.com>
@ 2003-02-19 20:26 ` Guy Fraser <guy@incentre.net>
0 siblings, 0 replies; 11+ messages in thread
From: Guy Fraser @ 2003-02-19 20:26 UTC (permalink / raw)
To: pgsql-sql
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.
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2003-02-19 20:26 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-14 04:09 Passing arrays Michael Weaver <mweaver@corpusglobe.com>
2003-02-14 04:14 ` Josh Berkus <josh@agliodbs.com>
2003-02-14 04:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-14 08:09 Re: [SQL] Passing arrays Michael Weaver <mweaver@corpusglobe.com>
2003-02-14 12:10 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-02-14 08:57 ` David Durst <ddurst@larubber.com>
2003-02-14 13:17 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-02-14 15:48 ` Joe Conway <mail@joeconway.com>
2003-02-19 19:06 ` Guy Fraser <guy@incentre.net>
2003-02-14 09:29 Re: Passing arrays Michael Weaver <mweaver@corpusglobe.com>
2003-02-19 20:26 ` Guy Fraser <guy@incentre.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox