Обсуждение: Inserting Using RowType

Is it possible to INSERT data into a table using a rowtype data type?  I
have a long (~5000 byte, 300 field) record *which I did not design* that
needs to be loaded into a table.  In my plsql function I accept a line
from a data file.  I have declared a ib837 variable to be of
my_table%rowtype and go about assigning each field to the ib837 record
using substr on the input field (yes, that's 329 rows, sigh).  What I
would then like to do is:

INSERT INTO my_table ib837;

Where the ib837 is of type my_table%rowtype.  Is this possible?  Is
there another -- easier/better -- way to load these record into the
table?  The input record is fixed-width, though it does not contain all
of the fields in the table (I add a timestamp and an id column).

Thanks for your help,
--greg

--
Greg Lindstrom               501 975.4859 (office)
Senior Programmer            501 219-4455 (fax)
NovaSys Health               greg.lindstrom@novasyshealth.com
Little Rock, Arkansas

"We are the music makers, and we are the dreamers of dreams."  W.W.

On Apr 27, 2005, at 12:19 PM, Greg Lindstrom wrote:

> What I would then like to do is:
>
> INSERT INTO my_table ib837;
>
> Where the ib837 is of type my_table%rowtype.  Is this possible?  Is
> there another -- easier/better -- way to load these record into the
> table?  The input record is fixed-width, though it does not contain
> all of the fields in the table (I add a timestamp and an id column).
>


ib837 does have all the fields of my_table if you defined it as
my_table%rowtype (even if you did not assign them all). So you could
use this:

INSERT INTO my_table VALUES (ib837.*);


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

On Wed, Apr 27, 2005 at 11:19:04AM -0500, Greg Lindstrom wrote:
>
> Is it possible to INSERT data into a table using a rowtype data type?  I
> have a long (~5000 byte, 300 field) record *which I did not design* that
> needs to be loaded into a table.  In my plsql function I accept a line
> from a data file.  I have declared a ib837 variable to be of
> my_table%rowtype and go about assigning each field to the ib837 record
> using substr on the input field (yes, that's 329 rows, sigh).  What I
> would then like to do is:
>
> INSERT INTO my_table ib837;

PostgreSQL 8.0 has better support for composite types than previous
versions.  For example, the following works for me in 8.0.2:

INSERT INTO my_table VALUES (ib837.*);

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Greg Lindstrom <greg.lindstrom@novasyshealth.com> writes:
> What I would then like to do is:

> INSERT INTO my_table ib837;

The trick is to get it to "burst" the rowtype value into separate columns.
I don't think you could get it to do that before 8.0, but this works
as of 8.0:

regression=# \d int8_tbl
   Table "public.int8_tbl"
 Column |  Type  | Modifiers
--------+--------+-----------
 q1     | bigint |
 q2     | bigint |

regression=# create function foo(bigint,bigint) returns void as $$
regression$# declare myrow int8_tbl;
regression$# begin
regression$#   myrow.q1 = $1;
regression$#   myrow.q2 = $2;
regression$#   insert into int8_tbl select (x).* from (select myrow as x) ss;
regression$#   return;
regression$# end$$ language plpgsql;
CREATE FUNCTION

            regards, tom lane

Michael Fuhr <mike@fuhr.org> writes:
> PostgreSQL 8.0 has better support for composite types than previous
> versions.  For example, the following works for me in 8.0.2:

> INSERT INTO my_table VALUES (ib837.*);

Ya know, for some reason I was convinced that wouldn't work, but it
does ... certainly a lot cleaner than the hack I just posted ...

            regards, tom lane

On Wed, Apr 27, 2005 at 02:08:33PM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> >
> > INSERT INTO my_table VALUES (ib837.*);
>
> Ya know, for some reason I was convinced that wouldn't work, but it
> does ... certainly a lot cleaner than the hack I just posted ...

Hmmm...so does it work by design or by accident?  I assume that if
it were by accident, your response would have been "Yeah, but...."

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike@fuhr.org> writes:
> On Wed, Apr 27, 2005 at 02:08:33PM -0400, Tom Lane wrote:
>> Michael Fuhr <mike@fuhr.org> writes:
>>> INSERT INTO my_table VALUES (ib837.*);
>>
>> Ya know, for some reason I was convinced that wouldn't work, but it
>> does ... certainly a lot cleaner than the hack I just posted ...

> Hmmm...so does it work by design or by accident?  I assume that if
> it were by accident, your response would have been "Yeah, but...."

No, it's by design --- in fact now I remember having made it happen
in 8.0.  But the ol hindbrain was remembering how things Used Ta Be.

            regards, tom lane