Page 1 of 2

dblink postgresql

Posted: Thu 13 Jun 2013 09:59
by lao
Hi,
i have problem to execute this commande with dblink and delphi 2007 (i try with unidac 4.6.12 and 5.0.1)
this request work in pgadmin3.

insert into tablea
select * from
dblink('host=hostname user=username dbname=dbnamepassword=mypassword',
'select * from tablea') As servertablea
(
a_id integer,
a_name varchar(25)
)
where not exists(select a_id from tablea where tablea.a_id=a_id)


regards

Re: dblink postgresql

Posted: Thu 13 Jun 2013 10:30
by lao
Hi,
the request is executed without problem but no data inserted.
Regards

Re: dblink postgresql

Posted: Thu 13 Jun 2013 12:22
by DemetrionQ
Hello.

  UniDAC just sends the SQL query to server without any changes. All data processing operations will be performed on the server side. We couldn't reproduce the problem. In our tests UniDAC sent a query similar to yours to the server - and data was inserted to the table successfully.

Please check the following:
 - the source table is not empty;
 - conditions for selection from the source table are set correctly;
 - you have rights to edit the table.

Re: dblink postgresql

Posted: Thu 13 Jun 2013 12:40
by lao
Hi,
thank you for your reply.
Yes everything is ok, and when i execute this command in pgadmin my local table is populated this records from server.
regards,

Re: dblink postgresql

Posted: Thu 13 Jun 2013 12:47
by lao
Hi, sorry, everything is working now.
reagrds

Re: dblink postgresql

Posted: Thu 13 Jun 2013 14:19
by DemetrionQ
If any other questions come up, please contact us.

Re: dblink postgresql

Posted: Tue 10 Dec 2013 10:44
by wusir01
Hi,
I've a problem with postgresql dblink. I'm working on database synchronization. I have one master database1 hosted on host1 and a slave database2 hosted on host2. the 2 databases have not the same tables columns (differences between tables data model). on each changes operated on database1 (hoste1) I want to replicate it on a specific column and specific table on database2 (host2) using dblink. can someone help me please ??

Re: dblink postgresql

Posted: Tue 10 Dec 2013 11:34
by AlexP
Hello,

For such synchronization, you can use triggers on the required tables in the master database and insert data in these triggers via dblink into the required tables and fields in the slave database.

Re: dblink postgresql

Posted: Tue 10 Dec 2013 11:44
by wusir01
yes how can i make the requet

i try this but not work

select dblink_connect_u('conn104', 'hostaddr=192.168.1.104 port=5432 dbname=db104 user=postgres password=postgres');

select dblink_connect_u('conn105', 'hostaddr=192.168.1.105 port=5432 dbname=db105 user=postgres password=postgres');


select dblink_exec('conn105',
'insert into db105.table1(col1, col2) select * from dblink('conn104', 'select * from db104.table2')
return (id int, colx varchar);');

Re: dblink postgresql

Posted: Tue 10 Dec 2013 14:53
by AlexP
Hello,

Below is a simple sample of a trigger with dblink:

Code: Select all

CREATE or replace FUNCTION dept_ins() RETURNS trigger AS $dept_ins$
    BEGIN
 perform dblink_connect('hostaddr=xxx.xxx.xxx.xxx port=5432 dbname=alexp user=postgres password=postgres');
 perform dblink_exec('insert into dept(deptno, dname, loc) values ('||NEW.deptno||','''||New.dname||''','''||New.loc||''');'); 
 perform dblink_disconnect();
        RETURN NEW;
    END;
$dept_ins$ LANGUAGE plpgsql;

CREATE TRIGGER dept_ins BEFORE INSERT ON dept
    FOR EACH ROW EXECUTE PROCEDURE dept_ins();

Re: dblink postgresql

Posted: Tue 10 Dec 2013 15:27
by wusir01
thanks for your reply

Re: dblink postgresql

Posted: Wed 11 Dec 2013 07:50
by AlexP
Hello,

If you have any other questions, feel free to contact us.

Re: dblink postgresql

Posted: Wed 11 Dec 2013 09:54
by wusir01
thanks very much, i adapt it to my configuration and it works fine.

Re: dblink postgresql

Posted: Wed 11 Dec 2013 10:19
by AlexP
Hello,

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.

Re: dblink postgresql

Posted: Thu 09 May 2019 03:27
by sham78
Hello..
I have a problem to access ad procedure/function from postgresql. I have 2 databases postgresql. To make both databases communicate each other, i have created a dblink. When i access table using dblink, success. For example access table. I use like this - select * from [email protected] when i want to access a function/procedure using same method, failed.