On 07/25/2012 07:25 PM, Chris Bartlett wrote:
I am not sure if this is expected behaviour or a bug.
Using PG 9.2 beta 2 and PGAdmin3 1.16 beta 2.
Connect as bob (superuser)
In public schema:
create table people (cols...)
create view people_view as select * from people
Create schema bob
create table bob.people (cols...)
create view bob.people_view as select * from people
(NB: view references people, not bob.people)
Insert a record into bob.people
Select * from bob.people_view
-> Nil result set
(expected to return the record from bob.people)
Check definition of bob.people_view
-> "create view bob.people_view as select * from public.people"
(NB: "from public.people" - compare "create view bob.people_view as
select * from people" above)
I had hoped/expected that a view would use the search path to find the
table it references. Why does bob.people_view reference public.people?
Is this a bug or expected behaviour? Do view definitions require
explicit reference to schema.table? My use case is that I effectively
want to define a default schema to be replicated for new tenants in a
multi-tenant system.
http://www.postgresql.org/docs/9.2/static/runtime-config-client.html
"
When objects are created without specifying a particular target schema,
they will be placed in the first valid schema named in search_path. An
error is reported if the search path is empty.
"
I am guessing if you do show search_path; from psql you will see that
the public schema is before the bob schema. The SELECT for the
unqualified people table in CREATE VIEW bob.people_view will find
public.people first in that case.
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
