[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2845
  • 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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