Re: Copying a row within table

On Wednesday 15 March 2006 03:11, John DeSoi wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

Hi,

Thank you guys, I'll try these.

Reason I need this is I have a largish product table which stores slightly
different types of products and for some types it is extremely handy to just
clone an existing product and then change the name and other few details
rather than go the time consuming way of creating a new almost identical
product from scratch filling in all the required values and calling all their
check routines in the application. Different types do not use all or the same
colums, some are left null or empty and some have different boolean values in
one or more columns.

Best regards and thanks again,

Aarni

--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core** linux system
--------------