Re: [SQL] passing a multiple join to a function?
On Mon, Dec 17, 2007 at 12:27:34PM -0500, Rodrigo De León wrote: > On 12/17/07, Louis-David Mitterrand <[EMAIL PROTECTED]> wrote: > > I've got this ugly case statement that I'd like to hide in a function: > > Why don't you hide the entire query in a VIEW? That is probably the best solution. Thanks ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] returning an array as a list fo single-column rows?
Hi, is there a way to return a Pg array as a list of single-column row values? I am trying to circumvent DBI's lack of support for native database arrays and return the list of values from an ENUM as a perl array. Thanks, ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] returning an array as a list fo single-column rows?
Hello try create or replace function unpack(anyarray) returns setof anyelement as $$ select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) g(i); $$ language sql; postgres=# select * from unpack(array[1,2,3,4]); unpack 1 2 3 4 (4 rows) Regards Pavel Stehule On 23/12/2007, Louis-David Mitterrand <[EMAIL PROTECTED]> wrote: > Hi, > > is there a way to return a Pg array as a list of single-column row > values? > > I am trying to circumvent DBI's lack of support for native database > arrays and return the list of values from an ENUM as a perl array. > > Thanks, > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] returning an array as a list fo single-column rows?
On Sun, Dec 23, 2007 at 10:19:26PM +0100, Pavel Stehule wrote: > Hello > > try > > create or replace function unpack(anyarray) > returns setof anyelement as $$ > select $1[i] > from generate_series(array_lower($1,1), array_upper($1,1)) g(i); > $$ language sql; > > postgres=# select * from unpack(array[1,2,3,4]); > unpack > > 1 > 2 > 3 > 4 > (4 rows) Beautiful. Thank you. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] returning an array as a list fo single-column rows?
On 23/12/2007, Louis-David Mitterrand <[EMAIL PROTECTED]> wrote: > Hi, > > is there a way to return a Pg array as a list of single-column row > values? > > I am trying to circumvent DBI's lack of support for native database > arrays and return the list of values from an ENUM as a perl array. > > Thanks, > you can solve this problem with conversion to string with const separator Like: postgres=# select array_to_string(array[1,2,3,4],'|'); array_to_string - 1|2|3|4 (1 row) [EMAIL PROTECTED] ~]$ perl @a = split(/\|/, "1|2|3"); print $a[1]; Regards Pavel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] returning an array as a list fo single-column rows?
On Sun, Dec 23, 2007 at 10:27:09PM +0100, Pavel Stehule wrote: > On 23/12/2007, Louis-David Mitterrand > <[EMAIL PROTECTED]> wrote: > > Hi, > > > > is there a way to return a Pg array as a list of single-column row > > values? > > > > I am trying to circumvent DBI's lack of support for native database > > arrays and return the list of values from an ENUM as a perl array. > > > > Thanks, > > > > you can solve this problem with conversion to string with const separator > > Like: > > postgres=# select array_to_string(array[1,2,3,4],'|'); > array_to_string > - > 1|2|3|4 > (1 row) > > [EMAIL PROTECTED] ~]$ perl > @a = split(/\|/, "1|2|3"); > print $a[1]; Yes I thought about it, but would rather have Pg do the array splitting. For instance if the separator occurs in an array element there is no built-in escaping: % select array_to_string(array['ee','dd','rr','f|f'],'|'); array_to_string - ee|dd|rr|f|f ... and then perl would have it all wrong. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] returning an array as a list fo single-column rows?
>
> Yes I thought about it, but would rather have Pg do the array splitting.
> For instance if the separator occurs in an array element there is no
> built-in escaping:
>
> % select array_to_string(array['ee','dd','rr','f|f'],'|');
> array_to_string
> -
> ee|dd|rr|f|f
if you have not some special char, then unpack is one possible solution
theoretically you can use text output
postgres=# select array['aa','aaa,j']::text;
array
-
{aa,"aaa,j"}
(1 row)
but nothing nice parse it :(
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] returning an array as a list of single-column rows... (different approach)
An: [email protected] Betreff: Re: [SQL] returning an array as a list fo single-column rows? The following will return the elements of an array each in its Own row. Using both array_lower() and array_upper() the number of array Elements and their internal index may vary from record to record. Or may even be absent. Within the record the array nstat[],nwert[],nwho[] must correspond. Joining the table with generate_series(array_lower(nWert,1),array_upper(nWert,1)) as indx returns the contained array elements. Considering the following table with array. Create table werte (id : integer, .. .. nstat : character(1)[], nwert : double precision[], nwho : character varying(9)[] ); select w.id,ii.indx, w.nStat[ii.indx],w.nWert[ii.indx],w.nWho[ii.indx] from werte w join ( select id, generate_series(array_lower(nWert,1),array_upper(nWert,1)) as indx from werte ) ii on ii.id=w.id ; Let me know what you think about this approach? My best regards, Stefan Becker ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
