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
[Postgresql Jobs] [Postgresql Admin] [Postgresql Performance] [Linux Clusters] [PHP Home] [PHP on Windows] [Programming PHP] [Kernel Newbies] [PHP Classes] [Find Someone Nice] [PHP Books] [PHP Databases] [Postgresql & PHP] [Yosemite]