Re: [mmoncure@gmail.com: Re: [GENERAL] array_to_set functions] - Mailing list pgsql-hackers

On Tue, 7 Aug 2007, Decibel! wrote:

> ISTM that having a built-in array_to_set function would be awfully
> useful... Is the aggregate method below an acceptable way to do it?

Umm, the array_to_set function is not an aggregate.  Personally, when I
need this functionality, I use this function conveniently present in the
default install:

select * from information_schema._pg_expandarray(ARRAY['foo', 'bar', 'baz']); x  | n
-----+---foo | 1bar | 2baz | 3
(3 rows)


Not exactly well documented or well known, but it works.

>
> ----- Forwarded message from Merlin Moncure <mmoncure@gmail.com> -----
> 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
>
> ---------------------------(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
>
>
> ----- End forwarded message -----
>
>

-- 
Mollison's Bureaucracy Hypothesis:If an idea can survive a bureaucratic review and be implementedit wasn't worth
doing.