rollback to savepoint issue

[Lorusso Domenico <domenico.l76@xxxxxxxxx>]

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