[ DBLink ] if fail , finished insert or send to another table.

Поиск
Список
Период
Сортировка
От Thomaz Luiz Santos
Тема [ DBLink ] if fail , finished insert or send to another table.
Дата
Msg-id CAHtRPB6jfAZmQa_6OCQurOPa-iBRz-=c1n+43qSkNfJepxUP_g@mail.gmail.com
обсуждение исходный текст
Список pgsql-cluster-hackers
Дерево обсуждения
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/

В списке pgsql-cluster-hackers по дате отправления:

Предыдущее
От: Josh Berkus
Дата:
Сообщение: Cluster Summit Agenda started
Следующее
От: Josh Berkus
Дата:
Сообщение: Please add discussion topics for cluster-hackers meeting