On Apr 5, 2006, at 3:37 AM, James Long wrote:
> Suppose I have a table "foo" with columns a b and c. A is a serial
> unique key.
>
> What's a good way to copy a record to a temp table, and then append it
> back in as a new record, automatically assigning a new serial key?
Here is an example using a function rather than a temp table.
create table foo (
a serial primary key,
b text,
c integer
);
create or replace function duplicate_foo(rec foo)
returns integer as $$
begin
rec.a := nextval(pg_get_serial_sequence('foo', 'a'));
insert into foo values (rec.*);
return rec.a;
end;
$$ language plpgsql;
insert into foo values (default, 'one', 100);
insert into foo values (default, 'two', 200);
select * from foo;
a | b | c
---+-----+-----
1 | one | 100
2 | two | 200
(2 rows)
-- duplicate all rows, or you could use a where clause to limit the
duplicated rows:
select duplicate_foo(foo) from foo;
duplicate_foo
---------------
3
4
select * from foo;
a | b | c
---+-----+-----
1 | one | 100
2 | two | 200
3 | one | 100
4 | two | 200
(4 rows)
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
