By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,369 Members | 950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,369 IT Pros & Developers. It's quick & easy.

How to make cross-database reference in Postgres

P: 4
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.
Dec 28 '06 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 534
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.
Dec 29 '06 #2

P: 4
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 ?
Jan 3 '07 #3

P: 2
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:
Aug 22 '15 #4

Post your reply

Sign in to post your reply or Sign up for a free account.