Hello guys,
I can't use the savepoint and rollback to savepoint clause.
I've found some similar problems around on the web, but I can't catch the good way to proceed.
What I'm trying to do is:
- compare new set of attribute with older
- if some attributes are part of old set and not in the new one: delete the old
- but if parameter "on_misisng_delete" is false rollback delete command and rais exception
The original code in embedded in a function, but the problem is the same:
ERROR: syntax error at or near "to"
LINE 41: rollback to savepoint deleteAttribute;
^
SQL state: 42601
Character: 768
Code extracted:
do $body$
declare
on_missing_delete boolean=false;
_i integer;
_vAttributeName text='paperi';
_importo integer= 5000;
begin
savepoint deleteAttribute;
execute format($$
with ru as (
update public.%I as q
set is_closed=true
, modify_user_id='process:definitionUpdate'
where q.importo > $1
returning 1
)
select count(*) from ru
;$$
, _vAttributeName)
using _importo
into _i;
--> If I can't delete and there are row to delete raise excpetion
if not on_missing_delete and _i > 0 then
--> undo deletion
rollback to savepoint deleteAttribute;
raise exception 'New attributes list foresees to remove % attribute(s), but parameter "on missing delete" is false.', _i;
else
release savepoint deleteAttribute;
end if;
rollback;
end;
$body$;
I know I can modify the code, but I wish to understand why I can't use rollback to save point