Обсуждение: array_to_set functions
David Fetter and I just came up with these, perhaps others will find them useful: CREATE OR REPLACE FUNCTION array_to_set(anyarray, int) RETURNS SETOF anyelement LANGUAGE SQL AS $$ SELECT $1[i] from generate_series(array_lower($1, $2), array_upper($1, $2)) i $$; CREATE OR REPLACE FUNCTION array_to_set(anyarray) RETURNS SETOF anyelement LANGUAGE SQL AS $$ SELECT array_to_set($1, 1) $$; -- Decibel!, aka Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Вложения
On 8/1/07, Decibel! <decibel@decibel.org> wrote: > David Fetter and I just came up with these, perhaps others will find > them useful: > > CREATE OR REPLACE FUNCTION array_to_set(anyarray, int) RETURNS SETOF anyelement LANGUAGE SQL AS $$ > SELECT $1[i] from generate_series(array_lower($1, $2), array_upper($1, $2)) i > $$; > CREATE OR REPLACE FUNCTION array_to_set(anyarray) RETURNS SETOF anyelement LANGUAGE SQL AS $$ > SELECT array_to_set($1, 1) > $$; very nice, although IMO there is a strong justification for these functions to be in core and written in C for efficiency (along with array_accum, which I have hand burn from copying and pasting out of the documentation). merlin
On Wed, 2007-08-01 at 07:14 +0530, Merlin Moncure wrote: > On 8/1/07, Decibel! <decibel@decibel.org> wrote: > > David Fetter and I just came up with these, perhaps others will find > > them useful: > > > > CREATE OR REPLACE FUNCTION array_to_set(anyarray, int) RETURNS SETOF anyelement LANGUAGE SQL AS $$ > > SELECT $1[i] from generate_series(array_lower($1, $2), array_upper($1, $2)) i > > $$; > > CREATE OR REPLACE FUNCTION array_to_set(anyarray) RETURNS SETOF anyelement LANGUAGE SQL AS $$ > > SELECT array_to_set($1, 1) > > $$; > > very nice, although IMO there is a strong justification for these > functions to be in core and written in C for efficiency (along with > array_accum, which I have hand burn from copying and pasting out of > the documentation). > > merlin > Excellent timing guys. :^) I was trying to build a function to list the items of an array, but ran into problems and was going to post what I had been working on. Your functions work great. In case you don't have the function to generate an array from a set here is one I have been using : CREATE AGGREGATE array_accum ( BASETYPE = anyelement, SFUNC = array_append, STYPE = anyarray, INITCOND = '{}' ); > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Guy Fraser Network Administrator The Internet Centre 1-888-450-6787 (780)450-6787
On 8/3/07, Guy Fraser <guy@incentre.net> wrote: > On Wed, 2007-08-01 at 07:14 +0530, Merlin Moncure wrote: > > On 8/1/07, Decibel! <decibel@decibel.org> wrote: > > > David Fetter and I just came up with these, perhaps others will find > > > them useful: > > > > > > CREATE OR REPLACE FUNCTION array_to_set(anyarray, int) RETURNS SETOF anyelement LANGUAGE SQL AS $$ > > > SELECT $1[i] from generate_series(array_lower($1, $2), array_upper($1, $2)) i > > > $$; > > > CREATE OR REPLACE FUNCTION array_to_set(anyarray) RETURNS SETOF anyelement LANGUAGE SQL AS $$ > > > SELECT array_to_set($1, 1) > > > $$; > > > > very nice, although IMO there is a strong justification for these > > functions to be in core and written in C for efficiency (along with > > array_accum, which I have hand burn from copying and pasting out of > > the documentation). > > > > merlin > > > Excellent timing guys. :^) > > I was trying to build a function to list the items of an array, but > ran into problems and was going to post what I had been working on. > > Your functions work great. > > In case you don't have the function to generate an array from a set > here is one I have been using : > > > CREATE AGGREGATE array_accum ( > BASETYPE = anyelement, > SFUNC = array_append, > STYPE = anyarray, > INITCOND = '{}' > ); I think that's what just about everyone uses. Unfortunately the reverse of the function (array_to_set above) AFAIK does not map directly to the C array API. merlin
On Sun, Aug 05, 2007 at 08:18:08PM +0530, Merlin Moncure wrote: > On 8/3/07, Guy Fraser <guy@incentre.net> wrote: > > On Wed, 2007-08-01 at 07:14 +0530, Merlin Moncure wrote: > > > On 8/1/07, Decibel! <decibel@decibel.org> wrote: > > > > David Fetter and I just came up with these, perhaps others will find > > > > them useful: > > > > > > > > CREATE OR REPLACE FUNCTION array_to_set(anyarray, int) RETURNS SETOF anyelement LANGUAGE SQL AS $$ > > > > SELECT $1[i] from generate_series(array_lower($1, $2), array_upper($1, $2)) i > > > > $$; > > > > CREATE OR REPLACE FUNCTION array_to_set(anyarray) RETURNS SETOF anyelement LANGUAGE SQL AS $$ > > > > SELECT array_to_set($1, 1) > > > > $$; > > > > > > very nice, although IMO there is a strong justification for these > > > functions to be in core and written in C for efficiency (along with > > > array_accum, which I have hand burn from copying and pasting out of > > > the documentation). > > > > > > merlin > > > > > Excellent timing guys. :^) > > > > I was trying to build a function to list the items of an array, but > > ran into problems and was going to post what I had been working on. > > > > Your functions work great. > > > > In case you don't have the function to generate an array from a set > > here is one I have been using : > > > > > > CREATE AGGREGATE array_accum ( > > BASETYPE = anyelement, > > SFUNC = array_append, > > STYPE = anyarray, > > INITCOND = '{}' > > ); > > I think that's what just about everyone uses. Unfortunately the > reverse of the function (array_to_set above) AFAIK does not map > directly to the C array API. Oh, cool, hadn't thought about using an aggregate to do this. That's probably faster than what I came up with. -- Decibel!, aka Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Вложения
On Tue, 2007-08-07 at 17:46 -0500, Decibel! wrote: > On Sun, Aug 05, 2007 at 08:18:08PM +0530, Merlin Moncure wrote: > > On 8/3/07, Guy Fraser <guy@incentre.net> wrote: > > > On Wed, 2007-08-01 at 07:14 +0530, Merlin Moncure wrote: > > > > On 8/1/07, Decibel! <decibel@decibel.org> wrote: > > > > > David Fetter and I just came up with these, perhaps others will find > > > > > them useful: > > > > > > > > > > CREATE OR REPLACE FUNCTION array_to_set(anyarray, int) RETURNS SETOF anyelement LANGUAGE SQL AS $$ > > > > > SELECT $1[i] from generate_series(array_lower($1, $2), array_upper($1, $2)) i > > > > > $$; > > > > > CREATE OR REPLACE FUNCTION array_to_set(anyarray) RETURNS SETOF anyelement LANGUAGE SQL AS $$ > > > > > SELECT array_to_set($1, 1) > > > > > $$; > > > > > > > > very nice, although IMO there is a strong justification for these > > > > functions to be in core and written in C for efficiency (along with > > > > array_accum, which I have hand burn from copying and pasting out of > > > > the documentation). > > > > > > > > merlin > > > > > > > Excellent timing guys. :^) > > > > > > I was trying to build a function to list the items of an array, but > > > ran into problems and was going to post what I had been working on. > > > > > > Your functions work great. > > > > > > In case you don't have the function to generate an array from a set > > > here is one I have been using : > > > > > > > > > CREATE AGGREGATE array_accum ( > > > BASETYPE = anyelement, > > > SFUNC = array_append, > > > STYPE = anyarray, > > > INITCOND = '{}' > > > ); > > > > I think that's what just about everyone uses. Unfortunately the > > reverse of the function (array_to_set above) AFAIK does not map > > directly to the C array API. > > Oh, cool, hadn't thought about using an aggregate to do this. That's > probably faster than what I came up with. You are welcome. I am glad someone appreciated my gesture.