Re: Convert Arbitrary Table to Array?

On Mon, Feb 9, 2009 at 4:14 PM, Harald Fuchs  wrote:
> In article <17050.1234200030@sss.pgh.pa.us>,
> Tom Lane  writes:
>
>> Lee Hughes  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