[SQL] plpgsql exception handling

[Uwe Bartels]

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