How to make cross-database reference in Postgres

[krithikas]

Hello,
We have two postgres databases running in the same server. Now we have to write a trigger in one database to access a table in another database for live data updation in both databases. How can we perform that?
Can we use contrib/dblink? If we yes how to do that? Our postgres version is 7.4.7. And the function dblink does not exist.How to do that?
Or is there a better way of doing this cross reference.

-Krithika.

[michaelb]

For installation look at contrib/dblink/README.dblink
If you don't have a source code you can download it from http://www.postgresql.org

I don't know if this would work for you, often things you can do from inside a trigger, or a function have certain limitations.

[krithikas]

I installed the postgresql-contrib package as it was available with my debian sarge 3.1 .
Then i just did
psql dbname < /usr/share/postgres/contrib/dblink.sql

I did some select operations using dblink and they worked perfectly.
When i tried to write a trigger in pl/pgsql like this ,

CREATE TRIGGER triggername BEFORE INSERT OR UPDATE ON testtable
FOR EACH ROW EXECUTE PROCEDURE test();

CREATE OR REPLACE FUNCTION test() RETURNS TRIGGER AS '
BEGIN
SELECT dblink_exec(''dbname=sample'',''insert into testtable values (NEW.x,NEW.y)'');
END;
' LANGUAGE plpgsql;

It threw an error like this,
ERROR: sql error
DETAIL: ERROR: NEW used in query that is not in a rule

Actually i want to insert the records that are getting inserted into testtable in my current db , also into a table testtable in another db 'sample' simultaneously. Is this the right way of doing this using dblink ?

[anvesh]

DbLink extension of PostgreSQL which is used to connect one database to another database.
I have prepared full demonstration on this. Also provide full script with example.
You must visit this post: