Re: Append to a GUC parameter ?

On Tue, Aug 5, 2014 at 10:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > BTW, while there's unlikely to be a good reason to put search_path in
> > pg.conf with appends, there are a LOT of reasons to want to be able to
> > append to it during a session.
>
> [shrug...] You can do that today with current_setting()/set_config().
>
>
+1

With a very simple statement you can do that:

fabrizio=# SELECT current_setting('search_path');
current_setting
-----------------
"$user",public
(1 row)

fabrizio=# SELECT set_config('search_path',
current_setting('search_path')||', another_schema', false);
set_config
--------------------------------
"$user",public, another_schema
(1 row)

Or you can create a simple sql function to concat your configs:

fabrizio=# CREATE FUNCTION concat_config(name TEXT, value TEXT, is_local
BOOLEAN)
fabrizio-# RETURNS text
fabrizio-# LANGUAGE sql
fabrizio-# AS $$ SELECT set_config(name, current_setting(name)||','||value,
is_local); $$;
CREATE FUNCTION
fabrizio=# SELECT concat_config('search_path', 'another_schema', false);
concat_config
-------------------------------
"$user",public,another_schema
(1 row)

fabrizio=# SELECT concat_config('search_path', 'schema2', false);
concat_config
---------------------------------------
"$user",public,another_schema,schema2
(1 row)

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello