On Sun, Oct 16, 2011 at 4:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
>> Now that you mention it, the following might actually already work:
>
>> WITH settings AS (
>> SELECT set_config('timezone', 'Europe/Amsterdam', t),
>> set_config('work_mem', '1 GB', t)
>> ),
>> foo AS (
>> SELECT …
>> )
>> INSERT INTO bar SELECT * FROM foo;
>
> Only for small values of "work" ... you won't be able to affect planner
> settings that way, nor can you assume that that WITH item is executed
> before all else. See recent thread pointing out that setting values
> mid-query is unsafe.
I previously floated the idea of using a new keyword, possibly LET,
for this, like this:
LET var = value [, ...] IN query
I'm not sure if anyone bought it, but I'll run it up the flagpole
again and see if anyone salutes. I tend to agree with the idea that
SET LOCAL isn't always what you want; per-transaction is not the same
as per-query, and multi-command query strings have funny semantics,
and multiple server round-trips are frequently undesirable; and it
just seems cleaner, at least IMHO.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company