How to set config param temporarily?

From: "Kynn Jones" <kynnjo(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to set config param temporarily?
Date: 2007-10-16 18:35:38
Message-ID: c2350ba40710161135x1f05f20fh64b85633b25d85e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'd like to set some config parameter "temporarily"; i.e. so that the
new setting is active, say, only during the execution of the next SQL
statement. This is the best I've come up with:

-- first, save the original setting of the parameter
CREATE TEMP TABLE save_config AS
SELECT setting FROM pg_settings WHERE name = 'some_param';

-- next, set the parameter to its temporary value
SET some_param = 'foobar';

-- ...yadda-yadda

-- restore the original setting
UPDATE pg_settings
SET setting = ( SELECT * FROM save_config )
WHERE name = 'some_param';

-- drop the temporary table
DROP TABLE save_config;

Is there a less laborious approach?

The root of needing to go through all this song and dance is that I
don't know of any way to set up a simple temporary variable to hold a
value. The temporary table is the closest I can come up to
implementing a temporary variable. Is there a simpler approach?

TIA!

kj

P.S. Even though the code above works, the Update statement prints out

UPDATE 0

at the end. This makes no sense to me; the condition clearly succeeds
once, since the update in fact happened, as one can easily confirm.
So why the "UPDATE 0" output?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stanislav Raskin 2007-10-16 18:53:50 Re: Updating a production database schema from dev server
Previous Message Michael Crozier 2007-10-16 18:35:20 Re: Updating a production database schema from dev server