Thread: plpgsql function with more than one array argument
Hi, thanks for reading this message. I have a table (in a postgres database) looking like this: Table "zdec_bhab"Attribute | Type | Modifier -----------+-----------+----------run | bigint |evt | bigint |...pcha | real[] |... where pcha is a 2D array, i.e. the first index can go from 1 to some number and the second is 1..3. Now, I'd like to create a plpgsql function taking as an argument two vectors (arrays) from pcha: CREATE FUNCTION mytest(real[],real[]) RETURNS real AS ' DECLARE p1 ALIAS FOR $1; p2 ALIAS FOR $2; begin -- RAISE NOTICE ''xxx %'',p2; return p2[1][1]; end;' LANGUAGE 'plpgsql'; I do the following query: select pcha[1:1][1:3],pcha[2:2][1:3],mytest(pcha[1:1][1:3],pcha[2:2][1:3]) from zdec_bhab where nch>=2; which yields: pcha | pcha | mytest ---------------------------------------------+---------------------------------------------+--------{{"-21.0788","35.0317","19.2111"}} | {{"21.0605","-34.995","-19.2111"}} | i.e. mytest seems to return something empty... however, If I uncomment the RAISE NOTICE line, I see the correct values (as in the output of the select statement). If I do select pcha[1:1][1:3],pcha[2:2][1:3],mytest(pcha[2:2][1:3],pcha[1:1][1:3]) from zdec_bhab where nch>=2; (i.e. the arguments of mytest exchanged), I get the correct values. Am I doing something wrong or is this a 'feature' ? (I'm using PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3). Is it possible in plpgsql to have functions with more than one array argument ? What about plperl ? Or do I have to convert the 2D array into three 1D arrays pcha_x, pcha_y and pcha_z ? best regards & thanks for the help, André
Array slice subscripts (was Re: plpgsql function with more than one array argument)
From
Tom Lane
Date:
Andre Holzner <Andre.Holzner@cern.ch> writes: > Am I doing something wrong or is this a 'feature' ? What's biting you is that the array slice operator uses the provided lower bounds in the resultant array. For example: regression=# select pcha from zdec_bhab; pcha ------------------------------------{{11,12,13},{21,22,23},{31,32,33}} (1 row) regression=# select array_dims(pcha) from zdec_bhab;array_dims ------------[1:3][1:3] (1 row) regression=# select pcha[2:2][1:3] from zdec_bhab; pcha --------------{{21,22,23}} (1 row) regression=# select array_dims(pcha[2:2][1:3]) from zdec_bhab;array_dims ------------[2:2][1:3] (1 row) So your function receives an array with first index starting at 2, which it's not expecting; its attempt to fetch element [1][1] is out of bounds and produces a NULL. Offhand this behavior seems like a misfeature: perhaps it'd be more sensible for the extracted slice to always have index lower bounds set to 1. But I'd like to see some discussion before changing it (and I don't plan to touch it before 7.2 release, in any case ;-)). Comments anyone? regards, tom lane
Is this a TODO item? --------------------------------------------------------------------------- Tom Lane wrote: > Andre Holzner <Andre.Holzner@cern.ch> writes: > > Am I doing something wrong or is this a 'feature' ? > > What's biting you is that the array slice operator uses the provided > lower bounds in the resultant array. For example: > > regression=# select pcha from zdec_bhab; > pcha > ------------------------------------ > {{11,12,13},{21,22,23},{31,32,33}} > (1 row) > > regression=# select array_dims(pcha) from zdec_bhab; > array_dims > ------------ > [1:3][1:3] > (1 row) > > regression=# select pcha[2:2][1:3] from zdec_bhab; > pcha > -------------- > {{21,22,23}} > (1 row) > > regression=# select array_dims(pcha[2:2][1:3]) from zdec_bhab; > array_dims > ------------ > [2:2][1:3] > (1 row) > > So your function receives an array with first index starting at 2, > which it's not expecting; its attempt to fetch element [1][1] is out > of bounds and produces a NULL. > > Offhand this behavior seems like a misfeature: perhaps it'd be more > sensible for the extracted slice to always have index lower bounds > set to 1. But I'd like to see some discussion before changing it > (and I don't plan to touch it before 7.2 release, in any case ;-)). > > Comments anyone? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Re: [HACKERS] Array slice subscripts (was Re: plpgsql function with more than one array argument)
From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Is this a TODO item? I guess so, since no one seems to have objected to the proposed change. It's a pretty trivial change; I'll take care of it. regards, tom lane > Tom Lane wrote: >> Andre Holzner <Andre.Holzner@cern.ch> writes: > Am I doing something wrong or is this a 'feature' ? >> >> What's biting you is that the array slice operator uses the provided >> lower bounds in the resultant array. For example: >> >> regression=# select pcha from zdec_bhab; >> pcha >> ------------------------------------ >> {{11,12,13},{21,22,23},{31,32,33}} >> (1 row) >> >> regression=# select array_dims(pcha) from zdec_bhab; >> array_dims >> ------------ >> [1:3][1:3] >> (1 row) >> >> regression=# select pcha[2:2][1:3] from zdec_bhab; >> pcha >> -------------- >> {{21,22,23}} >> (1 row) >> >> regression=# select array_dims(pcha[2:2][1:3]) from zdec_bhab; >> array_dims >> ------------ >> [2:2][1:3] >> (1 row) >> >> So your function receives an array with first index starting at 2, >> which it's not expecting; its attempt to fetch element [1][1] is out >> of bounds and produces a NULL. >> >> Offhand this behavior seems like a misfeature: perhaps it'd be more >> sensible for the extracted slice to always have index lower bounds >> set to 1. But I'd like to see some discussion before changing it >> (and I don't plan to touch it before 7.2 release, in any case ;-)). >> >> Comments anyone? >> >> regards, tom lane