pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedinsert rowtype
4+ messages / 2 participants
[nested] [flat]
* insert rowtype
@ 2003-05-08 02:23 Bryan Klimt <Bryan_Klimt@baylor.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Bryan Klimt @ 2003-05-08 02:23 UTC (permalink / raw)
To: pgsql-sql
In a plpgsql function, I have a variable of type table%rowtype
I populate the row by calling "select into <variable> * from <table>"
then i change some stuff like "<variable>.<field> := <new value>"
now i want to insert the row into the table as a new row - is there an
easy way to do this?
i know i could do "insert into <table> values ( <variable>.<field 1>,
<variable>.<field 2>, ... );"
but that is a lot of typing, and it seems like there should be an
easier way.
-Bryan
^ permalink raw reply [nested|flat] 4+ messages in thread
* insert rowtype
@ 2003-05-08 13:58 Bryan Klimt <Bryan_Klimt@baylor.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Bryan Klimt @ 2003-05-08 13:58 UTC (permalink / raw)
To: pgsql-sql
In a plpgsql function, I have a variable of type table%rowtype
I populate the row by calling "select into <variable> * from <table>"
then i change some stuff like "<variable>.<field> := <new value>"
now i want to insert the row into the table as a new row - is there an
easy way to do this?
i know i could do "insert into <table> values ( <variable>.<field 1>,
<variable>.<field 2>, ... );"
but that is a lot of typing, and it seems like there should be an
easier way.
-Bryan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: insert rowtype
@ 2003-05-08 16:41 Bryan Klimt <Bryan_Klimt@baylor.edu>
parent: Bryan Klimt <Bryan_Klimt@baylor.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Bryan Klimt @ 2003-05-08 16:41 UTC (permalink / raw)
To: pgsql-sql
So, to answer my own question...
I wanted to duplicate a row, but change a few data items in it.
Here is an example table to demonstrate:
# select oid,* from test;
oid | id | tmstamp | description
-------+----+---------------------+-------------
20396 | 1 | 0000-00-00-00-00-00 | hello
20397 | 2 | 0000-00-00-00-00-00 | goodbye
20398 | 2 | 0000-00-00-00-00-01 | goodbye
I wanted to create a new row like this:
oid | id | tmstamp | description
-------+----+---------------------+-------------
20396 | 1 | 0000-00-00-00-00-00 | hello
20397 | 2 | 0000-00-00-00-00-00 | goodbye
20398 | 2 | 0000-00-00-00-00-01 | goodbye
20415 | 2 | 1111-00-00-00-00-02 | goodbye
But with my real table there are like 50 columns, and i didn't want to
retype all their names.
So, I created this function:
create or replace function updatetest(integer,varchar(32)) returns
integer as
'
declare
-- the oid of the new row
newoid integer;
begin
-- duplicate the row
insert into test
select * from test t where
t.tmstamp=(
select max(tt.tmstamp) from test tt where tt.id=t.id
)
and t.id=$1;
-- get the oid of the new duplicate
get diagnostics newoid = result_oid;
-- update the columns you want to change
update test set tmstamp=$2 where oid=newoid;
-- return the new oid
return newoid;
end;
' language plpgsql;
# select updatetest(2,'1111-00-00-00-00-02');
updatetest
------------
20415
I know it looks kinda obvious but it took me forever to figure it out.
Specifically, "get diagnostics newoid = result_oid;" is not very
obvious to plpgsql newbies.
-Bryan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: insert rowtype
@ 2003-05-12 08:42 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 4+ messages in thread
From: Christoph Haller @ 2003-05-12 08:42 UTC (permalink / raw)
To: pgsql-sql; +Cc: Bryan_Klimt@baylor.edu
>
> create or replace function updatetest(integer,varchar(32)) returns
> integer as
> '
> declare
> -- the oid of the new row
> newoid integer;
> begin
> -- duplicate the row
> insert into test
> select * from test t where
> t.tmstamp=(
> select max(tt.tmstamp) from test
tt where tt.id=t.id
> )
> and t.id=$1;
>
> -- get the oid of the new duplicate
> get diagnostics newoid = result_oid;
>
> -- update the columns you want to change
> update test set tmstamp=$2 where oid=newoid;
>
> -- return the new oid
> return newoid;
> end;
> ' language plpgsql;
>
I only want to mention, this would insert multiple rows if the WHERE
conditions do not specifiy a single row.
Regards, Christoph
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-05-12 08:42 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-08 02:23 insert rowtype Bryan Klimt <Bryan_Klimt@baylor.edu>
2003-05-08 13:58 insert rowtype Bryan Klimt <Bryan_Klimt@baylor.edu>
2003-05-08 16:41 ` Bryan Klimt <Bryan_Klimt@baylor.edu>
2003-05-12 08:42 Re: insert rowtype Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox