Thread: CREATE VIEW question...

Hello there...
I have two databases, called db1 an db2. Database db1 contains a table
called memberlist.
Now I will create a view, called members, in database db2 (containig data
from table memberlist) with the following query:
CREATE VIEW members AS SELECT * FROM db1.memberlist;
->>> this error is displayed: ERROR; parser: error at or near "."
How can I create a view in one database with data from a second/different
database?
Jan Konzack


-----------------------------------------------------------------------
Riester-Rente - Kassieren Sie das Geld vom Staat:
Zum Förderungsrechner! http://www.arcor.de/home/redir.php/riesterrente
-----------------------------------------------------------------------

On Wed, 2002-05-01 at 20:53, j.konzack@arcor.de wrote:
> CREATE VIEW members AS SELECT * FROM db1.memberlist;
>
> ->>> this error is displayed: ERROR; parser: error at or near "."
>
> How can I create a view in one database with data from a second/different
> database?

You cannot.

When 7.3 comes out, you could implement this as separate schemas within
one database.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
    "For if ye forgive men their trespasses, your heavenly      Father will also forgive you; But if ye forgive not
men their trespasses, neither will your Father forgive     your trespasses."         Matthew 6:14,15  

At present, you cannot access data from another database, either with a
VIEW, or any other SQL statement.

You either want to: merge the two databases or dump/restore the needed table
to db2.

In PG 7.3 (still in development), namespaces have been added. While this
technically won't fix cross-database access, it will provide an excellent
solution to many tables in a database, and will provide an effective
solution to problems like this.

In the contrib/ directory (or using the contrib RPM, if that's your style),
you'll find "dblink", which provides a working but hackish way to access
other databases, but I'm not sure that it is ready for prime time.

HTH. Sorry for the bad news.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of j.konzack@arcor.de
> Sent: Wednesday, May 01, 2002 3:53 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] CREATE VIEW question...
>
>
> Hello there...
>
> I have two databases, called db1 an db2. Database db1 contains a table
> called memberlist.
> Now I will create a view, called members, in database db2 (containig data
> from table memberlist) with the following query:
>
> CREATE VIEW members AS SELECT * FROM db1.memberlist;
>
> ->>> this error is displayed: ERROR; parser: error at or near "."
>
> How can I create a view in one database with data from a second/different
> database?
>
> Jan Konzack
>
>
> -----------------------------------------------------------------------
> Riester-Rente - Kassieren Sie das Geld vom Staat:
> Zum Förderungsrechner! http://www.arcor.de/home/redir.php/riesterrente
> -----------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>