Re: 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 <sgendler(at)ideasculptor(dot)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(dot)bartels(at)gmail(dot)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
>>
>
>