dblink postgresql

[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

[lao]

Hi,
the request is executed without problem but no data inserted.
Regards

[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.

[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,

[lao]

Hi, sorry, everything is working now.
reagrds

[DemetrionQ]

If any other questions come up, please contact us.

[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 ??

[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.

[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);');

[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();

[wusir01]

thanks for your reply

[AlexP]

Hello,

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

[wusir01]

thanks very much, i adapt it to my configuration and it works fine.

[AlexP]

Hello,

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

[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.