Thread: How to set config param temporarily?

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?

Kynn Jones escribió:
> 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:

Did you try SET LOCAL?  It works per transaction rather than
per statement though.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Kynn Jones wrote:
> 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?

See the message thread "can I define own variable?" for a discussion of
how to create a temporarily variable.  Took place over the last week.

--
Guy Rouillier