Thread: accessing schemas

accessing schemas

From
"Mark Wilson"
Date:
Hi all,
 
I am using postgresql 7.2.1. 
 
I'm trying to create two separate objects with the same name, a table and a view on the table.  It seems like the best way to do this is to create two schemas and have the view reference the table in the other schema.  But I have a problem.  TO start with, it seems like psql is creating another database instead of another schema (e.g. CREATE SCHEMA TEST1; returns CREATE DATABASE).  This is not the biggest problem in the world, although I had thought that schemas would be subordinate to databases (just like users :~) 
 
Secondly, when I run "\c test2 testuser" to *connect* to the schema I want the view created in, where
running        create view try1 as select * from test1.try1;
returns        ERROR:  parser: parse error at or near "."
How can I get the view in the test2 schema to reference the table in schema test1?
 
Thanks in advance,
Mark

Re: accessing schemas

From
Mark Kirkwood
Date:
Mark,

You need 7.3 for schema support (7.3 is currently in Beta2 I think)

Best wishes

Mark
Mark Wilson wrote:

> Hi all,
>
> I am using postgresql 7.2.1.
>
> I'm trying to create two separate objects with the same name, a table
> and a view on the table.  It seems like the best way to do this is to
> create two schemas and have the view reference the table in the other
> schema.  But I have a problem.  TO start with, it seems like psql is
> creating another database instead of another schema (e.g. CREATE
> SCHEMA TEST1; returns CREATE DATABASE).  This is not the biggest
> problem in the world, although I had thought that schemas would be
> subordinate to databases (just like users :~)
>
> Secondly, when I run "\c test2 testuser" to *connect* to the schema I
> want the view created in, where
> running        create view try1 as select * from test1.try1;
> returns        ERROR:  parser: parse error at or near "."
> How can I get the view in the test2 schema to reference the table in
> schema test1?
>
> Thanks in advance,
> Mark





Re: accessing schemas

From
"Mark Wilson"
Date:
Sigh.  Thanks guys.

----- Original Message -----
From: "Mark Kirkwood" <markir@paradise.net.nz>
To: "Mark Wilson" <mark@mediasculpt.com>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, October 17, 2002 9:10 AM
Subject: Re: [GENERAL] accessing schemas


> Mark,
>
> You need 7.3 for schema support (7.3 is currently in Beta2 I think)
>
> Best wishes
>
> Mark
> Mark Wilson wrote:
>
> > Hi all,
> >
> > I am using postgresql 7.2.1.
> >
> > I'm trying to create two separate objects with the same name, a table
> > and a view on the table.  It seems like the best way to do this is to
> > create two schemas and have the view reference the table in the other
> > schema.  But I have a problem.  TO start with, it seems like psql is
> > creating another database instead of another schema (e.g. CREATE
> > SCHEMA TEST1; returns CREATE DATABASE).  This is not the biggest
> > problem in the world, although I had thought that schemas would be
> > subordinate to databases (just like users :~)
> >
> > Secondly, when I run "\c test2 testuser" to *connect* to the schema I
> > want the view created in, where
> > running        create view try1 as select * from test1.try1;
> > returns        ERROR:  parser: parse error at or near "."
> > How can I get the view in the test2 schema to reference the table in
> > schema test1?
> >
> > Thanks in advance,
> > Mark
>
>
>
>
>