Thread: plpgsql exception handling
Hi,<br /><br />I'm trying to run an execute with a dynamic sql command within a function.<br />I need a clean exception
handlinghere, but my version does not work somehow.<br /><br />I want to trap a possible syntax error and write the
errorcode, error message and the sql into a table.<br /><br />the function looks similar to this, I cut out the
unimportantpieces.<br /><br />CREATE OR REPLACE FUNCTION report_process(p_sql text)<br /> RETURNS integer AS<br
/>$BODY$<br/>DECLARE<br /> l_state smallint;<br /> l_message text;<br /> BEGIN<br /><br /> l_state=0;<br />
begin<br/> execute 'create table result_'||p_id||' as '||p_sql;<br /> exception when others then<br />
l_state=-3;<br /> l_message:='Error executing sql sql error code: %, sql error message: %, sql:
%',SQLSTATE,SQLERRM,l_sql;<br/> end;<br /><br /> update "cache" <br /> set c_date=now(), c_state=l_state,
c_message=l_message<br/> where c_id=p_id;<br /><br /> return 0;<br />END;<br />$BODY$<br /> LANGUAGE plpgsql
VOLATILESECURITY DEFINER<br /> COST 100;<br /><br /><br />This is the error message when I call the function<br
/>selectreport_process('select 1count(*) from event_log_day'::text);<br /><br /><br />ERROR: syntax error at or near
"("<br/>LINE 1: ...e table result_9 as select 1count(*) from d...<br />
^<br />QUERY: create table result_9 as select 1count(*)
fromevent_log_day<br />CONTEXT: PL/pgSQL function "report_process" line 31 at EXECUTE statement<br /><br />**********
Error**********<br /><br />ERROR: syntax error at or near "("<br />SQL state: 42601<br />Context: PL/pgSQL function
"report_process"line 31 at EXECUTE statement<br /><br />Any help is appreciated.<br /><br />best regards,<br />Uwe<br
/>
when I run 'select 1count(*) from table' in my postgres 8.4 installation, I get the exact same error message. Assuming the '1count()' function does exist, perhaps you need to full qualify it with a schema name?
It looks to me like the query you are passing to the procedure is invalid and is generating the error. Perhaps the 1 in front of count(*) is a typo?
On Wed, Mar 9, 2011 at 1:55 PM, Uwe Bartels <uwe.bartels@gmail.com> wrote:
Hi,
I'm trying to run an execute with a dynamic sql command within a function.
I need a clean exception handling here, but my version does not work somehow.
I want to trap a possible syntax error and write the error code, error message and the sql into a table.
the function looks similar to this, I cut out the unimportant pieces.
CREATE OR REPLACE FUNCTION report_process(p_sql text)
RETURNS integer AS
$BODY$
DECLARE
l_state smallint;
l_message text;
BEGIN
l_state=0;
begin
execute 'create table result_'||p_id||' as '||p_sql;
exception when others then
l_state=-3;
l_message:='Error executing sql sql error code: %, sql error message: %, sql: %',SQLSTATE,SQLERRM,l_sql;
end;
update "cache"
set c_date=now(), c_state=l_state, c_message=l_message
where c_id=p_id;
return 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
This is the error message when I call the function
select report_process('select 1count(*) from event_log_day'::text);
ERROR: syntax error at or near "("
LINE 1: ...e table result_9 as select 1count(*) from d...
^
QUERY: create table result_9 as select 1count(*) from event_log_day
CONTEXT: PL/pgSQL function "report_process" line 31 at EXECUTE statement
********** Error **********
ERROR: syntax error at or near "("
SQL state: 42601
Context: PL/pgSQL function "report_process" line 31 at EXECUTE statement
Any help is appreciated.
best regards,
Uwe
On Wed, Mar 9, 2011 at 2:08 PM, Samuel Gendler <sgendler@ideasculptor.com> wrote: > when I run 'select 1count(*) from table' in my postgres 8.4 installation, I > get the exact same error message. Assuming the '1count()' function does > exist, perhaps you need to full qualify it with a schema name? > It looks to me like the query you are passing to the procedure is invalid > and is generating the error. Perhaps the 1 in front of count(*) is a typo? Also seem to be missing "p_id" from your execute statement: execute 'create table result_'|| p_id ||' as '||p_sql;
Hi,
Yes, of course is this sql producing an error.
The point is, I want to trap the error and handle it. Here in this case I set the variable l_state and l_message.
But....
The function exits with an exception instead of returning. So the exception statement does not work as I think i would.
And I don't know why.
Best...
Uwe
PS: p_id is a variable in my code which is bigger. so ignore the update statement.
Yes, of course is this sql producing an error.
The point is, I want to trap the error and handle it. Here in this case I set the variable l_state and l_message.
But....
The function exits with an exception instead of returning. So the exception statement does not work as I think i would.
And I don't know why.
Best...
Uwe
PS: p_id is a variable in my code which is bigger. so ignore the update statement.
On 9 March 2011 23:08, Samuel Gendler <sgendler@ideasculptor.com> wrote:
when I run 'select 1count(*) from table' in my postgres 8.4 installation, I get the exact same error message. Assuming the '1count()' function does exist, perhaps you need to full qualify it with a schema name?It looks to me like the query you are passing to the procedure is invalid and is generating the error. Perhaps the 1 in front of count(*) is a typo?On Wed, Mar 9, 2011 at 1:55 PM, Uwe Bartels <uwe.bartels@gmail.com> wrote:Hi,
I'm trying to run an execute with a dynamic sql command within a function.
I need a clean exception handling here, but my version does not work somehow.
I want to trap a possible syntax error and write the error code, error message and the sql into a table.
the function looks similar to this, I cut out the unimportant pieces.
CREATE OR REPLACE FUNCTION report_process(p_sql text)
RETURNS integer AS
$BODY$
DECLARE
l_state smallint;
l_message text;
BEGIN
l_state=0;
begin
execute 'create table result_'||p_id||' as '||p_sql;
exception when others then
l_state=-3;
l_message:='Error executing sql sql error code: %, sql error message: %, sql: %',SQLSTATE,SQLERRM,l_sql;
end;
update "cache"
set c_date=now(), c_state=l_state, c_message=l_message
where c_id=p_id;
return 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
This is the error message when I call the function
select report_process('select 1count(*) from event_log_day'::text);
ERROR: syntax error at or near "("
LINE 1: ...e table result_9 as select 1count(*) from d...
^
QUERY: create table result_9 as select 1count(*) from event_log_day
CONTEXT: PL/pgSQL function "report_process" line 31 at EXECUTE statement
********** Error **********
ERROR: syntax error at or near "("
SQL state: 42601
Context: PL/pgSQL function "report_process" line 31 at EXECUTE statement
Any help is appreciated.
best regards,
Uwe
yes, p_id is a variable in my code which is bigger. so ignore the update statement.
Uwe
Uwe
On 10 March 2011 01:20, bricklen <bricklen@gmail.com> wrote:
On Wed, Mar 9, 2011 at 2:08 PM, Samuel GendlerAlso seem to be missing "p_id" from your execute statement:
<sgendler@ideasculptor.com> wrote:
> when I run 'select 1count(*) from table' in my postgres 8.4 installation, I
> get the exact same error message. Assuming the '1count()' function does
> exist, perhaps you need to full qualify it with a schema name?
> It looks to me like the query you are passing to the procedure is invalid
> and is generating the error. Perhaps the 1 in front of count(*) is a typo?
execute 'create table result_'|| p_id ||' as '||p_sql;
On Thu, Mar 10, 2011 at 12:45 AM, Uwe Bartels <uwe.bartels@gmail.com> wrote:
Hi,
Yes, of course is this sql producing an error.
The point is, I want to trap the error and handle it. Here in this case I set the variable l_state and l_message.
Doh! Of course. Sorry about that. What happens when you explicitly catch the 42601 sql state or the syntax_error condition instead of 'others?'
same same.
all errors including syntax_error match to others, but I checked it again. and the exception remains.
Uwe
all errors including syntax_error match to others, but I checked it again. and the exception remains.
Uwe
On 10 March 2011 10:56, Samuel Gendler <sgendler@ideasculptor.com> wrote:
On Thu, Mar 10, 2011 at 12:45 AM, Uwe Bartels <uwe.bartels@gmail.com> wrote:Hi,
Yes, of course is this sql producing an error.
The point is, I want to trap the error and handle it. Here in this case I set the variable l_state and l_message.Doh! Of course. Sorry about that. What happens when you explicitly catch the 42601 sql state or the syntax_error condition instead of 'others?'
On Thu, Mar 10, 2011 at 2:53 AM, Uwe Bartels <uwe.bartels@gmail.com> wrote:
same same.
all errors including syntax_error match to others, but I checked it again. and the exception remains.
I'm just guessing here, but is it throwing a new exception in the exception handler? I realize that the exception that is getting caught at the top level is the original exception, but maybe that's expected behaviour when an exception handler throws a new exception - it re-throws the original? What happens if you simplify the exception handler down to just a really simple log message? There's really nothing else obviously wrong with the code you provided, so I'm hoping it is a problem in the code that you didn't include since nothing else makes sense.
I just executed this and it worked correctly - saw my error statement from the exception handler:
CREATE OR REPLACE FUNCTION report_process(p_sql text)
RETURNS integer AS
$BODY$
DECLARE
l_state smallint;
l_message text;
BEGIN
l_state=0;
begin
execute 'create table result_9 as '||p_sql;
exception when others then
l_state=-3;
raise notice 'error';
end;
return 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
cost 100;
# select report_process('select 1count(*) from accounts'::text);
NOTICE: error
report_process
----------------
0
(1 row)
stupid me.
I edited a function with the same name, but different parameter types and tested with the other function.
so everything works fine.
thanks everybody for help.
best regards,
Uwe
I edited a function with the same name, but different parameter types and tested with the other function.
so everything works fine.
thanks everybody for help.
best regards,
Uwe
On 10 March 2011 11:53, Uwe Bartels <uwe.bartels@gmail.com> wrote:
same same.
all errors including syntax_error match to others, but I checked it again. and the exception remains.
UweOn 10 March 2011 10:56, Samuel Gendler <sgendler@ideasculptor.com> wrote:On Thu, Mar 10, 2011 at 12:45 AM, Uwe Bartels <uwe.bartels@gmail.com> wrote:Hi,
Yes, of course is this sql producing an error.
The point is, I want to trap the error and handle it. Here in this case I set the variable l_state and l_message.Doh! Of course. Sorry about that. What happens when you explicitly catch the 42601 sql state or the syntax_error condition instead of 'others?'