To: "Don Maier" <dMaier@genome.stanford.edu>, pgsql-sql@postgresql.org
Subject: Re: Expressing a result set as an array (and vice versa)?
References: <F3E0C44C-6118-41C6-AC71-2B19258F4F07@genome.stanford.edu>
Message-ID: <op.s6xxmukycigqcu@apollo13>
Date: Sat, 25 Mar 2006 00:17:08 +0100
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <F3E0C44C-6118-41C6-AC71-2B19258F4F07@genome.stanford.edu>
User-Agent: Opera M2/8.51 (Linux, build 1462)


CREATE OR REPLACE FUNCTION foreach( liste INTEGER[] ) RETURNS SETOF  
INTEGER AS $$
DECLARE
   i INTEGER;
BEGIN
   FOR i IN 1..icount(liste) LOOP
     RETURN NEXT liste[i];
   END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE array_accum (
     sfunc = array_append,
     basetype = anyelement,
     stype = anyarray,
     initcond = '{}'
);

SELECT array_accum( DISTINCT list_id ) FROM bookmarks;
   array_accum
---------------
  {1,2,3,4,5,7}

SELECT * FROM foreach( '{1,2,3,4,5,7}' );
  foreach
---------
        1
        2
        3
        4
        5
        7


On Thu, 23 Mar 2006 20:44:32 +0100, Don Maier <dMaier@genome.stanford.edu>  
wrote:

> Good day,
>
> Is it possible to construct an array from an appropriate select  
> expression that generates a result set of unknown cardinality?
> To focus on the simple case:  Is it possible to construct a one- 
> dimensional array from a select of a single column in a table with an  
> unknown number of rows?
>
> Conversely, is it possible to construct a (single column) result set  
> from a select expression on a one-dimensional array with an unknown  
> number of elements?
>
> Thanks for any hints!
>
> Regards,
> Don Maier
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly