Solved

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

Posted on 2007-11-30
4
2,778 Views
Last Modified: 2013-12-07
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
Comment
Question by:sean-keys
  • 2
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 20385981
See the source distribution directory /contrib/dblink
0
 

Author Comment

by:sean-keys
ID: 20386439
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
 
LVL 22

Expert Comment

by:earth man2
ID: 20386601
If the two databases do not need to be on separate machines you can put the two tables in two different SCHEMA
0
 

Author Comment

by:sean-keys
ID: 20386686
I'm sure I'll make it work one way or the other.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question