Thread: Array to rowset function

I have a function to convert a single dimension array to a row set

CREATE OR REPLACE FUNCTION arraytotable(anyarray)
  RETURNS SETOF anyelement AS
'
DECLARE

theData ALIAS FOR $1;
count integer;
start integer;

BEGIN
      start :=array_lower(theData,1);
      count :=array_upper(theData,1);
      IF start is not null THEN
      FOR i IN start..count LOOP
            RETURN NEXT theData[i];
      END LOOP;
      END IF;

RETURN;
END
'
  LANGUAGE 'plpgsql' IMMUTABLE STRICT;

Is there a more efficient way I can do this and are there any user defined
C functions available for this sort of array manipulation?

Thanks for any help
Laurie


:.________________
CONFIDENTIALITY : This  e-mail  and  any attachments are confidential and
may be privileged. If  you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another person, use
it for any purpose or store or copy the information in any medium.

 John Hansen posted a C implementaion of the UNNEST function that does
this.  It is in the archives here:
http://archives.postgresql.org/pgsql-hackers/2004-11/msg00158.php and
http://archives.postgresql.org/pgsql-hackers/2004-11/msg00327.php .
I've been using it for the last few months and it's been working great
for me.

On Mon, 17 Jan 2005 14:03:56 +0000, laurie.burrow@powerconv.alstom.com
<laurie.burrow@powerconv.alstom.com> wrote:
> I have a function to convert a single dimension array to a row set
>
> CREATE OR REPLACE FUNCTION arraytotable(anyarray)
>   RETURNS SETOF anyelement AS
> '
> DECLARE
>
> theData ALIAS FOR $1;
> count integer;
> start integer;
>
> BEGIN
>       start :=array_lower(theData,1);
>       count :=array_upper(theData,1);
>       IF start is not null THEN
>       FOR i IN start..count LOOP
>             RETURN NEXT theData[i];
>       END LOOP;
>       END IF;
>
> RETURN;
> END
> '
>   LANGUAGE 'plpgsql' IMMUTABLE STRICT;
>
> Is there a more efficient way I can do this and are there any user defined
> C functions available for this sort of array manipulation?
>
> Thanks for any help
> Laurie
>
> :.________________
> CONFIDENTIALITY : This  e-mail  and  any attachments are confidential and
> may be privileged. If  you are not a named recipient, please notify the
> sender immediately and do not disclose the contents to another person, use
> it for any purpose or store or copy the information in any medium.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>


--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org