|
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@xxxxxxxxx > To: hari.fuchs@xxxxxxxxx > CC: pgsql-general@xxxxxxxxxxxxxx > > On Mon, Feb 9, 2009 at 4:14 PM, Harald Fuchs <hari.fuchs@xxxxxxxxx> wrote: > > In article <17050.1234200030@xxxxxxxxxxxxx>, > > Tom Lane <tgl@xxxxxxxxxxxxx> writes: > > > >> Lee Hughes <lee@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Windows Live™: E-mail. Chat. Share. Get more ways to connect. Check it out. |
