On Tue, Aug 07, 2007 at 10:18:32PM -0700, Jeremy Drake wrote: > 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
Sorry, should have been more clear... array_to_set as shown below isn't,
but array_accum is, and does the opposite (set_to_array).
> 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 | 1
> bar | 2
> baz | 3
> (3 rows)
>
> Not exactly well documented or well known, but it works.
Worse than that, that's something that's entirely internal and could
change at any release. The fact that it exists for info_schema indicates
further need for these functions to exist in the backend.
> >
> > ----- Forwarded message from Merlin Moncure <[EMAIL PROTECTED]> -----
> > On 8/3/07, Guy Fraser <[EMAIL PROTECTED]> wrote:
> > > On Wed, 2007-08-01 at 07:14 +0530, Merlin Moncure wrote:
> > > > On 8/1/07, Decibel! <[EMAIL PROTECTED]> 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 implemented
> it wasn't worth doing.
>
--
Decibel!, aka Jim Nasby [EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
pgpFDiqJIchwf.pgp
Description: PGP signature
