View definition and schema search path bug or expected behaviour?

From: Chris Bartlett <c(dot)bartlett(at)paradise(dot)net(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: View definition and schema search path bug or expected behaviour?
Date: 2012-07-26 02:25:49
Message-ID: p06240806cc365805b15e@[192.168.200.4]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2012-07-26 02:37:56 Re: View definition and schema search path bug or expected behaviour?
Previous Message Craig Ringer 2012-07-26 00:13:26 Re: Smaller data types use same disk space