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

Sofer, Yuval wrote:
> last_message := dblink_error_message('dbname=postgres
> user=postgres password=manager') ;


> Am I not using dblink_error_message correctly?

Yes, you are not using dblink_error_message correctly. From the docs:

================================================== ================
Name

dblink_error_message -- gets last error message on the named connection

Synopsis

dblink_error_message(text connname) RETURNS text

Inputs

connname
The specific connection name to use.

Outputs

Returns last error message.

Example usage

SELECT dblink_error_message('dtest1');
================================================== ================

It requires a named connection, you are trying to use an anonymous one.

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings