hello! pgAdmins :D
I am trying to use the dblink in a trigger, however when the computer (source) that is running the triggers are not accessible by ethernet to the target computer, the dblink returns me an error and the trigger is not executed as planned, it is terminated and input records in the table are not written.
my idea is that the insert is done in the table on the local computer if the local computer has access to ethernet it should send the data to another computer using the dblink, but do not have access ethernet writes in the local table.
thank you.
INSERT INTO teste(id, valor) VALUES (1,'valor'); -- On Ethernet, connected OK
INSERT INTO teste(id, valor) VALUES (2,'valor'); -- Off Ethernet, disconnected ERROR.
Error:
ERRO: could not establish connection
DETAIL: não pôde conectar ao servidor: No route to host (0x00002751/10065)
O servidor está executando na máquina "192.168.102.23" e aceitando
conexões TCP/IP na porta 5432?
CONTEXT: comando SQL "SELECT (select count(*) from dblink_exec('host=192.168.102.23 port=5432 dbname=teste password=admin ', '' || $1 || '', false))"
PL/pgSQL function "senddatato" line 6 at atribuição
comando SQL "SELECT (SELECT SendDataTo(SQL))"
PL/pgSQL function "teste_after_insert" line 8 at PERFORM
********** Error **********
ERRO: could not establish connection
SQL state: 08001
Context: comando SQL "SELECT (select count(*) from dblink_exec('host=192.168.102.23 port=5432 dbname=teste password=admin ', '' || $1 || '', false))"
PL/pgSQL function "senddatato" line 6 at atribuição
comando SQL "SELECT (SELECT SendDataTo(SQL))"
PL/pgSQL function "teste_after_insert" line 8 at PERFORM
Code:
CREATE DATABASE teste
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'Portuguese, Brazil'
LC_CTYPE = 'Portuguese, Brazil'
CONNECTION LIMIT = -1;
CREATE TABLE teste
(
id bigint NOT NULL,
valor text,
CONSTRAINT teste_pkey PRIMARY KEY (id )
)
WITH (
OIDS=FALSE
);
ALTER TABLE teste
OWNER TO postgres;
CREATE OR REPLACE FUNCTION senddatato(sql text)
RETURNS integer AS
$BODY$
DECLARE
ServerON int;
BEGIN
ServerON := (select count(*) from dblink_exec('host=192.168.102.23 port=5432 dbname=teste password=admin ', '' || $1 || '', false));
return ServerON;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION senddatatoserver(text)
OWNER TO postgres;
CREATE OR REPLACE FUNCTION teste_after_insert()
RETURNS trigger AS
$BODY$
DECLARE
SQL text;
BEGIN
SQL := 'insert into teste(id,valor) values (' || NEW.id || ',' || '''' || NEW.valor || '''' || ')';
IF (true) THEN
PERFORM(SELECT SendDataTo(SQL));
RETURN NEW;
END IF;
RETURN NEW;
RAISE NOTICE 'gravado local!';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION teste_after_insert()
OWNER TO postgres;
--
------------------------------Thomaz Luiz Santos
Linux User: #359356
http://thomaz.santos.googlepages.com/
