pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
cannot get error message after dblink_exec execution
2+ messages / 2 participants
[nested] [flat]

* cannot get error message after dblink_exec execution
@ 2007-08-19 13:51  Sofer, Yuval <Yuval_Sofer@bmc.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Sofer, Yuval @ 2007-08-19 13:51 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Abraham, Danny <danny_abraham@bmc.com>

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

 

 

 

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: cannot get error message after dblink_exec execution
@ 2007-08-19 16:50  Joe Conway <mail@joeconway.com>
  parent: Sofer, Yuval <Yuval_Sofer@bmc.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Joe Conway @ 2007-08-19 16:50 UTC (permalink / raw)
  To: Sofer, Yuval <Yuval_Sofer@bmc.com>; +Cc: pgsql-admin@postgresql.org, "Abraham, Danny" <danny_abraham@bmc.com>

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2007-08-19 16:50 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-19 13:51 cannot get error message after dblink_exec execution Sofer, Yuval <Yuval_Sofer@bmc.com>
2007-08-19 16:50 ` Joe Conway <mail@joeconway.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox