Re: copy record?

On Thu, May 26, 2011 at 3:23 PM, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com> wrote:
> Hi:
>
>
>
> From within a perl/DBI script, I want to be able to make a copy of a record
> in a table, changing only the value of the primary key.  I don't know in
> advance what all the columns are, just the table name.
>
>
>
> I suppose I could pull the column names for the table from the metadata,
> query the table/record for the values to copy, build an insert statement
> from all of that and run it.  But is there a simpler, more elegant way to do
> this?

there's a very easy way using the composite type method as long as you
know which field(s) are the primary key -- especially if it's say the
first column and an integer.

postgres=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
Indexes:
"foo_a_b_idx" btree (a, b)

postgres=# select foo from foo limit 1;
foo
-------
(1,1)
(1 row)

change 1 -> 2 textually, cast the text back to the composite and pass it back in

insert into foo select (($$(2,1)$$::foo).*);

merlin