Re: Copying a row within table

On Mar 14, 2006, at 2:19 AM, Aarni Ruuhimäki wrote:

> testing=# INSERT INTO foo (foo_1, foo_2, foo_3 ...) (SELECT foo_1,
> foo_2,
> foo_3 ... FROM message_table WHERE foo_id = 10);
> INSERT 717286 1
> testing=#
>
> Is there a fast way to copy all but not the PK column to a new row
> within the
> same table so that the new foo_id gets its value from the sequence ?

Here is an example using a plpgsql function:

create or replace function test_duplicate (p_id integer)
returns integer as $$
declare
tt test%rowtype;
begin
select into tt * from test where id = p_id;
tt.id := nextval(pg_get_serial_sequence('test', 'id'));
insert into test values (tt.*);
return tt.id;
end;
$$ language plpgsql;

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