perform join on 2 DB

Question

I have 2 DB on the same server with the same user:

• ckan_default
• datastore_default

The relation of ckan_default is:

 Schema |             Name              | Type  |  Owner   
 -------+-------------------------------+-------+----------
 public | resource                      | table | ckanuser
 public | resource_group                | table | ckanuser
 public | package                       | table | ckanuser
 ....

The relation of datastore_default is:

  Schema |                 Name                 | Type  |  Owner   
  -------+--------------------------------------+-------+----------
  public | 1bc7932e-2507-467b-8c12-c9f321b760f7 | table | ckanuser
  public | 449138df-e089-41f2-8939-dcee53a31bc1 | table | ckanuser
  public | 7235f781-1b16-4abf-ac04-8d68fa62e432 | table | ckanuser
  ....

I wont to JOIN the 2 DB ON ckan_default.resource.id = datastore_default."NAME OF RELATION".

How?

Answer 1

PL/Proxy is another option, similar to dblink. I have used it in the past to talk between servers, where my use-case was a poor-man's distributed database cluster. The data on the the other servers was pulled in for certain large reports and it worked pretty well. The servers were all in the same colocation though, so if the other databases are geographically spread out then you are going to pay an additional penalty for network latency and data transfer times.

Answer 2

I dont think you can.

You can use dblink extension to query database B from A, but the query will be separated from the data context of database A.. this is how postgresql works.

EDIT: you can populate a view from the result of a dblink query, and then use it:

CREATE VIEW myremote_pg_proc AS
  SELECT *
    FROM dblink('dbname=postgres', 'select proname, prosrc from pg_proc')
    AS t1(proname name, prosrc text);
SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';

Examples in the link i posted.