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
/>
