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: [email protected]
> To: [email protected]
> CC: [email protected]
>
> On Mon, Feb 9, 2009 at 4:14 PM, Harald Fuchs <[email protected]> wrote:
> > In article <[email protected]>,
> > Tom Lane <[email protected]> writes:
> >
> >> Lee Hughes <[email protected]> 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 ([email protected])
> 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
