Re: Convert Arbitrary Table to Array?

the array needs to be named to a different name than the original table e.g.
CREATE OR REPLACE TYPE DOUBLE_NUMBER AS OBJECT (num1 NUMBER,num2 NUMBER);

--Create an Array which will use 2 of the double_numbers we just created
CREATE OR REPLACE TYPE TABLE_ARRAY AS VARRAY(2) OF DOUBLE_NUMBER;

--Create a Table which will use the t_array
CREATE TABLE mytable (

id INTEGER NOT NULL,

t_array TABLE_ARRAY NOT NULL,

PRIMARY KEY (id)

);

--A Table is now created which contains the 2 element ARRAY type structure as a column
INSERT INTO mytable VALUES(
1, TABLE_ARRAY(DOUBLE_NUMBER(1, 10),DOUBLE_NUMBER(1,15)));

SET DESCRIBE DEPTH ALL
SELECT id,m.t_array FROM mytable m;

SQL> SET DESCRIBE DEPTH ALL
SQL> SELECT id,m.t_array FROM mytable m;

ID
----------
T_ARRAY(NUM1, NUM2)
--------------------------------------------------------------------------------

1
TABLE_ARRAY(DOUBLE_NUMBER(1, 10), DOUBLE_NUMBER(1, 15))

HTH
Martin

______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.

> Date: Tue, 10 Feb 2009 09:23:55 -0500
> Subject: Re: [GENERAL] Convert Arbitrary Table to Array?
> From: mmoncure(at)gmail(dot)com
> To: hari(dot)fuchs(at)gmail(dot)com
> CC: pgsql-general(at)postgresql(dot)org
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
Windows Live™: E-mail. Chat. Share. Get more ways to connect.
http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_AE_Faster_022009