Обсуждение: [ DBLink ] if fail , finished insert or send to another table.
I wonder if it is possible to validate the dblink in a function or trigger, to see whether it was sent or not the information to the server.
Case not sent i need the insert finished, or in another table make the insert, in the local database.
thanks! |
plpgsql / SQL:
CREATE TABLE teste(
id bigint,
valor text,
CONSTRAINT teste_pkey PRIMARY KEY (id)
)
CREATE FUNCTION SendDataToServer(SQL text ) RETURNS boolean AS $SendDataToServer$
BEGIN
IF (true) THEN
PERFORM(SELECT dblink('host=192.168.102.23 port=5432 dbname=teste password=admin ', '' || $1 || '', false));
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$SendDataToServer$ LANGUAGE plpgsql;
CREATE or REPLACE FUNCTION teste_after_insert() RETURNS trigger AS $teste_after_insert$
DECLARE
SQL text;
BEGIN
IF (true) THEN
SQL := 'insert into teste(id,valor) values (' || NEW.id || ',' || '''' || NEW.valor || '''' || ')';
PERFORM(SELECT SendDataToServer(SQL));
ELSE
RAISE NOTICE 'ERRRO!';
END IF;
RETURN NEW;
END;
$teste_after_insert$ LANGUAGE plpgsql;
CREATE TRIGGER teste_after_insert AFTER INSERT ON teste
FOR EACH ROW EXECUTE PROCEDURE teste_after_insert();
insert into teste(id,valor) values(1,'tufao');
error messages:
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 dblink('host=192.168.102.23 port=5432 dbname=teste password=admin ', '' || $1 || '', false))"
PL/pgSQL function "senddatatoserver" line 4 at PERFORM
comando SQL "SELECT (SELECT SendDataToServer(SQL))"
PL/pgSQL function "teste_after_insert" line 7 at PERFORM
********** Error **********
ERRO: could not establish connection
SQL state: 08001
Context: comando SQL "SELECT (SELECT dblink('host=192.168.102.23 port=5432 dbname=teste password=admin ', '' || $1 || '', false))"
PL/pgSQL function "senddatatoserver" line 4 at PERFORM
comando SQL "SELECT (SELECT SendDataToServer(SQL))"
PL/pgSQL function "teste_after_insert" line 7 at PERFORM
------------------------------
Thomaz Luiz Santos
Linux User: #359356
http://thomaz.santos.googlepages.com/