From: | Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | plpgsql exception handling |
Date: | 2011-03-09 21:55:18 |
Message-ID: | AANLkTi=Qm80a+x0-CY0LYjuns-wux18Usf5xmhXKGExD@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Samuel Gendler | 2011-03-09 22:08:39 | Re: plpgsql exception handling |
Previous Message | Dmitriy Igrishin | 2011-03-09 18:07:53 | Re: How to transform table rows into Colum? |