How to inherit search_path from template

From: Phui Hock <phuihock(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to inherit search_path from template
Date: 2010-09-09 11:13:09
Message-ID: 668b6126-c958-4479-9fad-4426045181b0@x24g2000pro.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martín Marqués 2010-09-09 11:59:00 NOT IN vs. OUTER JOIN and NOT NULL
Previous Message Ungermann Carsten 2010-09-09 10:55:08 Regular expression in an if-statement will not work