array_to_column function
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!
В Сбт, 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>
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!
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