Обсуждение: 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