Solved

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

Posted on 2007-11-30
4
2,737 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
Comment Utility
See the source distribution directory /contrib/dblink
0
 

Author Comment

by:sean-keys
Comment Utility
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
Comment Utility
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
Comment Utility
I'm sure I'll make it work one way or the other.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now