Thread: to copy a record

All,

I want to copy a record, changing only a few fields.  I was going to do
this with a plpgsql function, just by selecting the record I want copied
into a variable, changing the field I need, and doing an INSERT


My problem is that I'd like to do it without having to explicitly name each
field in the INSERT  I'd like to do something like
    ...
    DECLARE
        copyRec RECORD;

    BEGIN
        SELECT INTO copyRec...

        copyRec.field1 = newValue;

        INSERT INTO myTable VALUES (copyRec)  <---

and nor have to list each field like:

    VALUES (copyRec.field1, copyRec.field2...)

Any way of doing that?

Thanks

Take care,
Jay

SELECT * INTO TEMP temp_table FROM blah;
UPDATE temp_table SET field = newvalue;
INSERT INTO blah SELECT * FROM temp_table;

On Wed, Jun 04, 2003 at 11:36:36AM -0700, Jay O'Connor wrote:
> All,
>
> I want to copy a record, changing only a few fields.  I was going to do
> this with a plpgsql function, just by selecting the record I want copied
> into a variable, changing the field I need, and doing an INSERT
>
>
> My problem is that I'd like to do it without having to explicitly name each
> field in the INSERT  I'd like to do something like
>     ...
>     DECLARE
>         copyRec RECORD;
>
>     BEGIN
>         SELECT INTO copyRec...
>
>         copyRec.field1 = newValue;
>
>         INSERT INTO myTable VALUES (copyRec)  <---
>
> and nor have to list each field like:
>
>     VALUES (copyRec.field1, copyRec.field2...)
>
> Any way of doing that?
>
> Thanks
>
> Take care,
> Jay
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On 2003.06.04 12:48 Jim C. Nasby wrote:
> SELECT * INTO TEMP temp_table FROM blah;
> UPDATE temp_table SET field = newvalue;
> INSERT INTO blah SELECT * FROM temp_table;


Looks sweet.  Much simpler what I was doing, too


Thanks

Take care,
Jay

On Wed, Jun 04, 2003 at 03:50:29PM -0700, Jay O'Connor wrote:
> On 2003.06.04 12:48 Jim C. Nasby wrote:
> > SELECT * INTO TEMP temp_table FROM blah;
> > UPDATE temp_table SET field = newvalue;
> > INSERT INTO blah SELECT * FROM temp_table;
>
>
> Looks sweet.  Much simpler what I was doing, too

BTW (I should have mentioned this originally), this will surely be much
slower than doing a SELECT INTO and specifying the fields individually,
because off the overhead of 2 additional queries, as well as creating
the temporary table.
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"