Re: Convert Arbitrary Table to Array?

On Mon, Feb 9, 2009 at 4:14 PM, Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com> wrote:
> In article <17050(dot)1234200030(at)sss(dot)pgh(dot)pa(dot)us>,
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
>> Lee Hughes <lee(at)hughesys(dot)com> writes:
>>> Hi, I need a function that accepts a table name and returns a 2-dimensional
>>> array of the table data.
>
>> Well, in 8.3 and up there are arrays of composite types, so you can
>> do something like
>
>> select array(select mytable from mytable);
>
> Interesting. On 8.3.5 I tried
>
> CREATE TABLE mytable (
> id serial NOT NULL,
> mytable int NOT NULL,
> PRIMARY KEY (id)
> );
>
> INSERT INTO mytable VALUES
> (1, 10),
> (2, 20),
> (3, 30),
> (4, 40),
> (5, 50),
> (6, 60);
>
> SELECT array(SELECT mytable FROM mytable);
>
> and it returned
>
> {10,20,30,40,50,60}
>
> Only when I renamed the second column from "mytable" to "mytablex" I got
>
> {"(1,10)","(2,20)","(3,30)","(4,40)","(5,50)","(6,60)"}
>
> as you promised. Is there any syntax for treating the first "mytable"
> as a composite type name instead of a column name?
>

SELECT array(SELECT m FROM mytable m);

There was a thread about this recently about how this my apply in
insert/update situation.

merlin