Обсуждение: cannot get error message after dblink_exec execution

Hi,

I am using DBLink contrib module.

I cannot catch the dblink_exec error messages.

On the other hand, the pgadmin gui shows the error message under “DETAIL” section:

The test function :

CREATE OR REPLACE FUNCTION test_func1()                                   

RETURNS integer AS

$$

DECLARE

stmt text;

conn text;

err text ;

last_message text default 'aaa';

BEGIN

          conn := 'dbname=postgres user=postgres password=manager';

          stmt := 'drop table not_existing_table';

          err := dblink_exec(conn, stmt,false);         

          last_message := dblink_error_message('dbname=postgres user=postgres password=manager') ;

          raise notice ' err is %',err; 

          raise notice ' last_message is %',last_message; 

          return 0;

END;

$$

LANGUAGE 'plpgsql' VOLATILE;

When I execute select test_func1(); I get the error message from the gui (table “not_existing table” does not exist):

NOTICE:  sql error

DETAIL:  ERROR:  table "not_existing_table" does not exist

CONTEXT:  PL/pgSQL function "test_func1" line 11 at assignment

NOTICE:   err is ERROR

NOTICE:   last_message is

Total query runtime: 100 ms.

1 rows retrieved.

My questions :

How can catch this error into the stored procedure parameter?

Am I not using dblink_error_message correctly?

I don’t mind retrieving the error message as the gui does, but how can I do it?

Thanks

Yuval

DBA team

BMC Software