Re: [SQL] Expressing a result set as an array (and vice versa)?

[PFC]

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 <[EMAIL PROTECTED]>  
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 [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly



---------------------------(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