Re: [SQL] plpgsql exception handling
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.
On 9 March 2011 23:08, Samuel Gendler 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 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
>>
>
>
Re: [SQL] plpgsql exception handling
yes, p_id is a variable in my code which is bigger. so ignore the update statement. Uwe On 10 March 2011 01:20, bricklen wrote: > On Wed, Mar 9, 2011 at 2:08 PM, Samuel Gendler > 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; >
Re: [SQL] plpgsql exception handling
On Thu, Mar 10, 2011 at 12:45 AM, Uwe Bartels 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?'
Re: [SQL] plpgsql exception handling
same same. 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 wrote: > > > On Thu, Mar 10, 2011 at 12:45 AM, Uwe Bartels 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?' > > >
Re: [SQL] plpgsql exception handling
On Thu, Mar 10, 2011 at 2:53 AM, Uwe Bartels 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)
Re: [SQL] plpgsql exception handling
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 On 10 March 2011 11:53, Uwe Bartels wrote: > same same. > 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 wrote: > >> >> >> On Thu, Mar 10, 2011 at 12:45 AM, Uwe Bartels 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?' >> >> >> >
