Tips for re-using function results within single insert
using this table:
create table test_table (a text, b text);
This would be nice to be able to do...
insert into test_table (a,b) select random()::text as "myrandom",
encode("myrandom",'base64');
Any ideas on how to accomplish this without
create table test_table (id serial, a text, b text);
insert into test_table (a) values (random()::text);
update test_table set b=encode(a,'base64') where id = currval('id_seq');
CG
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
On Friday 20 Sep 2002 5:52 pm, Chris Gamache wrote:
This would be nice to be able to do...
insert into test_table (a,b) select random()::text as "myrandom",
encode("myrandom",'base64');
Well, I'd be tempted to write a wrapper function tagged as "iscachable" and
call it with a parameter of the current transaction-id (see docs on trigger
functions) or current time (the one from now() which doesn't change per
transaction). If this gives you grief, try a standard constant.
This means PG will cache results as long as the parameter doesn't change, in
this case for the duration of the transaction.
HTH
- Richard Huxton
On Friday 20 Sep 2002 5:52 pm, Chris Gamache wrote:
This would be nice to be able to do...
insert into test_table (a,b) select random()::text as "myrandom",
encode("myrandom",'base64');
You can do this sort of thing with a level of subselect:
insert into test_table (a,b)
select myrandom, encode(myrandom, 'base64')
from (select random()::text as myrandom) ss;
Trying to let one SELECT output expression refer to another one on the
same level strikes me as a really bad idea, though.
regards, tom lane