From: PFC <lists@peufeu.com>
To: Don Maier <dMaier@genome.stanford.edu>
To: pgsql-sql@postgresql.org
Subject: Re: Expressing a result set as an array (and vice versa)?
Date: Sat, 25 Mar 2006 00:17:08 +0100
Message-ID: <op.s6xxmukycigqcu@apollo13> (raw)
In-Reply-To: <F3E0C44C-6118-41C6-AC71-2B19258F4F07@genome.stanford.edu>
References: <F3E0C44C-6118-41C6-AC71-2B19258F4F07@genome.stanford.edu>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@peufeu.com, dMaier@genome.stanford.edu
Subject: Re: Expressing a result set as an array (and vice versa)?
In-Reply-To: <op.s6xxmukycigqcu@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox