Обсуждение: Convert Arbitrary Table to Array?

Hi, I need a function that accepts a table name and returns a 2-dimensional array of the table data.

I found some related posts on this and other forums and tried several approaches with plpgsql but have had no success.

I know I can pull the table data out to the application tier and transform it from there but I would really prefer to keep it in the database.

Any help is greatly appreciated.

Thanks-

Lee

Lee Hughes <lee@hughesys.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);

However you are not going to be able to encapsulate that as a function
real well, because of the question of what is the function's result
type.

You should also realize that this approach is not going to scale to
large tables.  You don't want to get into pushing around arrays of more
than say a couple of megabytes.

Personally I'd be wondering exactly where this requirement comes from
and whether it doesn't betoken severe database-ignorance in the
application design.

            regards, tom lane

Good point on scalability -- I am planning to limit the number of rows fetched through LIMIT or a Cursor once I have the basics working.

Did you mean select array(select * from mytable);  ?

Thanks for your help, I will try this approach.

Lee




--
Lee
503-753-7620

Lee Hughes <lee@hughesys.com> writes:
> Good point on scalability -- I am planning to limit the number of rows
> fetched through LIMIT or a Cursor once I have the basics working.

Er ... why don't you just use a cursor directly on the table?

> Did you mean *select array(select * from mytable);*  ?

No, I meant what I wrote --- * won't work, you need a single composite
column coming out of the inner select.

            regards, tom lane

In article <17050.1234200030@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:

> Lee Hughes <lee@hughesys.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?

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

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@gmail.com
> To: hari.fuchs@gmail.com
> CC: pgsql-general@postgresql.org
>
> On Mon, Feb 9, 2009 at 4:14 PM, Harald Fuchs <hari.fuchs@gmail.com> wrote:
> > In article <17050.1234200030@sss.pgh.pa.us>,
> > Tom Lane <tgl@sss.pgh.pa.us> writes:
> >
> >> Lee Hughes <lee@hughesys.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@postgresql.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. Check it out.