Re: How to create database link and synonym in postgresql 9.0 - Mailing list pgsql-general

From Filip Rembiałkowski
Subject Re: How to create database link and synonym in postgresql 9.0
Date
Msg-id CAP_rwwnS3f3jgc-th3M6QrajtPPWmU7NfZHBB8onUABoVQB0cg@mail.gmail.com
Whole thread Raw
In response to How to create database link and synonym in postgresql 9.0  (tushar nehete <tpnehete@gmail.com>)
Responses Re: How to create database link and synonym in postgresql 9.0
List pgsql-general

There is no CREATE SYNONYM in PostgreSQL and it's not planned for implementation.
There is also no direct support for foreign data wrapper. But it's planned for 9.2.

Nearest  that you can get with PostgreSQL 9.0 is cautious use of dblink and views and rules.

here's a sample script to show what I mean:



create database db1;
create database db2;

\c db2;
create table emp ( id integer primary key, name text);
insert into emp values (1,'Joe'), (2,'Mary');

\c db1
\i /usr/share/postgresql/9.0/contrib/dblink.sql

SELECT dblink_connect( 'db2', 'dbname=db2' );

-- remote SELECT;
CREATE VIEW emp AS SELECT id,name FROM dblink('db2','SELECT id,name FROM emp') AS emp(id integer, name text);

SELECT * FROM emp ORDER BY name;

-- remote INSERT:
CREATE RULE emp_ins AS ON INSERT TO emp DO INSTEAD
SELECT dblink_exec( 'db2',
    'INSERT INTO emp(id,name) VALUES(' || quote_nullable(NEW.id) || ',' || quote_nullable(NEW.name) || ')', true );

INSERT INTO emp VALUES(3,'Phil');

-- remote UPDATE:
CREATE RULE emp_upd AS ON UPDATE TO emp DO INSTEAD
SELECT dblink_exec( 'db2',
    'UPDATE emp SET id=' || quote_nullable(NEW.id) || ', name=' || quote_nullable(NEW.name)
    || ' WHERE id=' || quote_nullable(OLD.id), true );

UPDATE emp SET name = 'Philip' WHERE id = 3;




2011/10/5 tushar nehete <tpnehete@gmail.com>
Hi,
In one of the migration porject  want to access and update some tables from
other database on same postgresql server.

The question is how to access tables from other database on the same postgres server.
If it is by database link like Oracle then what is the syntax.
And how to create synonym?

Say i have a postgres server on RHEL5 and it has 2 databases db1 and db2.
db2 has table emp which i want to access from db1.
so i create a dblink in oracle named dblnk_emp and access the table in db1 by
select * from emp@dblnk_emp;
so i create synonym for emp@dblnk_emp as emp in db1.


In postgres I can access table from other database by dblink but cannot update it. Also there is
support for synonym.

Please help..



pgsql-general by date:

Previous
From: Raghavendra
Date:
Subject: Re: Restoring 2 Tables From All Databases Backup
Next
From: Lauri Kajan
Date:
Subject: Re: null values in a view