Postgres Pro
Downloads
Home   >   mailing lists

Thread: array_to_column function

array_to_column function

From
David Fetter
Date:
Kind people,

Here's something I came up with, having accidentally discovered the
ARRAY() constructor (BTW, I think at least some pointer to it should
be in the array section of functions & operators).

CREATE OR REPLACE FUNCTION array_to_column (ANYARRAY)
RETURNS SETOF ANYELEMENT
IMMUTABLE
LANGUAGE plpgsql
AS $$
BEGIN   IF (position('][' IN array_dims($1)) <> 0)   THEN       RAISE EXCEPTION 'Only 1-dimensional arrays are
allowed!';  END IF;   FOR i IN array_lower($1, 1) .. array_upper($1, 1)   LOOP       RETURN NEXT $1[i];   END LOOP;
RETURN;
END;
$$;

Thanks to Markus Bertheau aka ska-fan for help with removing an
unneeded regex compare and with spelling. :)

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Re: array_to_column function

From
Markus Bertheau
Date:
В Сбт, 30.10.2004, в 21:54, David Fetter пишет:
> Kind people,

> CREATE OR REPLACE FUNCTION array_to_column (ANYARRAY)

You want to see that function distributed with PostgreSQL? It would
probably have to be implemented in C then, because PL/pgSQL-support has
to be explicitly "enabled" for every database.

--
Markus Bertheau <twanger@bluetwanger.de>

Re: array_to_column function

From
David Fetter
Date:
On Sat, Oct 30, 2004 at 11:55:48PM +0200, Markus Bertheau wrote:
> В Сбт, 30.10.2004, в 21:54, David Fetter пишет:
> > Kind people,
> 
> > CREATE OR REPLACE FUNCTION array_to_column (ANYARRAY)
> 
> You want to see that function distributed with PostgreSQL?  It would
> probably have to be implemented in C then, because PL/pgSQL-support
> has to be explicitly "enabled" for every database.

Yes, that would be good.  It would be nice to have some symmetry with
the ARRAY() function.  Also, it would be a handy thing for doing
column-like operations (aggregates, e.g.) on 1-D arrays :)

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Re: array_to_column function

From
Joe Conway
Date:
David Fetter wrote:
> On Sat, Oct 30, 2004 at 11:55:48PM +0200, Markus Bertheau wrote:
>>>CREATE OR REPLACE FUNCTION array_to_column (ANYARRAY)
>>
>>You want to see that function distributed with PostgreSQL?  It would
>>probably have to be implemented in C then, because PL/pgSQL-support
>>has to be explicitly "enabled" for every database.
> 
> Yes, that would be good.  It would be nice to have some symmetry with
> the ARRAY() function.  Also, it would be a handy thing for doing
> column-like operations (aggregates, e.g.) on 1-D arrays :)

This has actually been discussed and rejected nearly two years ago -- 
see this thread:  http://archives.postgresql.org/pgsql-hackers/2002-12/msg00453.php

Later, Peter Eisentraut pointed out that there is actually a 
spec-compliant way (UNNEST) to achieve the same result -- see this thread:
http://archives.postgresql.org/pgsql-hackers/2003-06/msg01167.php

I have the beginnings (at least) of a C function to do this somewhere, 
but have obviously not been able to find the time to implement it (yet). 
If you want to pursue this functionality for 8.1, UNNEST support is the 
way to go.

Joe