Lists: | pgsql-general |
---|
From: | Decibel! <decibel(at)decibel(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | array_to_set functions |
Date: | 2007-08-01 01:04:59 |
Message-ID: | 20070801010458.GE25704@nasby.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | Decibel! <decibel(at)decibel(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: array_to_set functions |
Date: | 2007-08-01 01:44:50 |
Message-ID: | b42b73150707311844k430a9350wc49cb5be73541f31@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 8/1/07, Decibel! <decibel(at)decibel(dot)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
From: | Guy Fraser <guy(at)incentre(dot)net> |
---|---|
To: | PGSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: array_to_set functions |
Date: | 2007-08-02 19:31:18 |
Message-ID: | 1186083078.21121.70.camel@sigurd.incentre.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Wed, 2007-08-01 at 07:14 +0530, Merlin Moncure wrote:
> On 8/1/07, Decibel! <decibel(at)decibel(dot)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(at)postgresql(dot)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
From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | "Guy Fraser" <guy(at)incentre(dot)net> |
Cc: | "PGSQL General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: array_to_set functions |
Date: | 2007-08-05 14:48:08 |
Message-ID: | b42b73150708050748l4917750cv55d853b82d5ef343@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 8/3/07, Guy Fraser <guy(at)incentre(dot)net> wrote:
> On Wed, 2007-08-01 at 07:14 +0530, Merlin Moncure wrote:
> > On 8/1/07, Decibel! <decibel(at)decibel(dot)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
From: | Decibel! <decibel(at)decibel(dot)org> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | Guy Fraser <guy(at)incentre(dot)net>, PGSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: array_to_set functions |
Date: | 2007-08-07 22:46:09 |
Message-ID: | 20070807224609.GX25704@nasby.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Sun, Aug 05, 2007 at 08:18:08PM +0530, Merlin Moncure wrote:
> On 8/3/07, Guy Fraser <guy(at)incentre(dot)net> wrote:
> > On Wed, 2007-08-01 at 07:14 +0530, Merlin Moncure wrote:
> > > On 8/1/07, Decibel! <decibel(at)decibel(dot)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(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
From: | Guy Fraser <guy(at)incentre(dot)net> |
---|---|
To: | PGSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: array_to_set functions |
Date: | 2007-08-08 16:13:04 |
Message-ID: | 1186589585.21121.110.camel@sigurd.incentre.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)incentre(dot)net> wrote:
> > > On Wed, 2007-08-01 at 07:14 +0530, Merlin Moncure wrote:
> > > > On 8/1/07, Decibel! <decibel(at)decibel(dot)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.