• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2869
  • Last Modified:

Creating a view of two tables from two different databases in postgresql

I have this MYSQL code.  This code creates a view in database asterisk and reads from another databse called openser.

CREATE VIEW vmusers AS
SELECT phplib_id as uniqueid,
  username as customer_id,
  'default' as context,
  username as mailbox,
  vmail_password as password,
  CONCAT(first_name,' ',last_name) as fullname,
  email_address as email,
  NULL as pager,
  datetime_created as stamp
FROM openser.subscriber;

I'm trying to figure out how to translate that into Postgresql code.  I have looked around but cant seem to find any docs on how to accomplish this.

Right now I get
ERROR: schema "openser" does not exist
SQL state: 3F000
When I try to execute the above code in postgres.
0
sean-keys
Asked:
sean-keys
  • 2
  • 2
1 Solution
 
earth man2Commented:
See the source distribution directory /contrib/dblink
0
 
sean-keysAuthor Commented:
The two databases exist on the same server.  Isn't there a way to do it like it was done in MySql  (FROM openser.subscriber; " ?   Dblink looks like it will perform the task, I was just hoping for a cleaner way.
0
 
earth man2Commented:
If the two databases do not need to be on separate machines you can put the two tables in two different SCHEMA
0
 
sean-keysAuthor Commented:
I'm sure I'll make it work one way or the other.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now