Re: copy record?

On Thu, May 26, 2011 at 3:23 PM, Gauthier, Dave <dave.gauthier@intel.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