Re: How to append records into a file which has serial unique IDs?

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