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.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org