Thread: How to inherit search_path from template

Hi,
How can I create a database template with altered search_path to be
inherited by child databases? Say, I created a template named
template_a with the following commands:

# CREATE DATABASE template_a WITH TEMPLATE template1;
# UPDATE pg_database SET datistemplate=true WHERE
datname='template_a';
# \c template_a
template_a=# CREATE SCHEMA test;
template_a=# GRANT ALL ON SCHEMA test TO PUBLIC;
template_a=# show search_path;
  search_path
----------------
 "$user",public
(1 row)

template_a=# ALTER DATABASE template_a SET search_path = test,public;


When I try to create a new database with the previous template,
search_path is not inherited.
test_a=# \dn
        List of schemas
        Name        |  Owner
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 public             | postgres
 test               | postgres
(6 rows)

test_a=# CREATE DATABASE test_a WITH TEMPLATE template_a;
test_a=# \c test_a
psql (8.4.4)
You are now connected to database "test_a".
test_a=# show search_path;
  search_path
----------------
 "$user",public
(1 row)

I've searched the archives for clue but of no avail. Any help or
advice is greatly appreciated.

On Thu, Sep 9, 2010 at 7:13 AM, Phui Hock <phuihock@gmail.com> wrote:
> Hi,
> How can I create a database template with altered search_path to be
> inherited by child databases? Say, I created a template named
> template_a with the following commands:

It doesn't really work that way -- GUC values are global,  not per
database.  You can however set them on the role and use them that way
-- that will probably work in your case.  Just make a role for each
database.  Another thing you can do is have your app immediately set
your search path upon login -- not a connection pooler friendly
approach though.

merlin

That answers a lot of my doubts with regard to search_path. Thanks!

On Thu, Sep 9, 2010 at 7:41 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Thu, Sep 9, 2010 at 7:13 AM, Phui Hock <phuihock@gmail.com> wrote:
>> Hi,
>> How can I create a database template with altered search_path to be
>> inherited by child databases? Say, I created a template named
>> template_a with the following commands:
>
> It doesn't really work that way -- GUC values are global,  not per
> database.

You can do "alter database xyz set search_path=..."

On Sun, Sep 12, 2010 at 8:34 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Sep 9, 2010 at 7:41 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Thu, Sep 9, 2010 at 7:13 AM, Phui Hock <phuihock@gmail.com> wrote:
>>> Hi,
>>> How can I create a database template with altered search_path to be
>>> inherited by child databases? Say, I created a template named
>>> template_a with the following commands:
>>
>> It doesn't really work that way -- GUC values are global,  not per
>> database.
>
> You can do "alter database xyz set search_path=..."

so you can...thanks for that!

merlin