Re: plpgsql exception handling - Mailing list pgsql-sql
From | Uwe Bartels |
---|---|
Subject | Re: plpgsql exception handling |
Date | |
Msg-id | AANLkTikvbFpV_aNwYq6sLExp5PmuWKWgf7p5A68X5NwC@mail.gmail.com Whole thread Raw |
In response to | Re: plpgsql exception handling (Samuel Gendler <sgendler@ideasculptor.com>) |
Responses |
Re: plpgsql exception handling
(Samuel Gendler <sgendler@ideasculptor.com>)
|
List | pgsql-sql |
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.
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@ideasculptor.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.bartels@gmail.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