rollback to savepoint issue

From: Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: rollback to savepoint issue
Date: 2023-09-04 09:51:30
Message-ID: CAJMpnG7DAcz4ckE_GGN4VkjaJvzVrdwCYwjH+ocLnVVYvb-fcQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message wheels 2023-09-04 10:15:42 Efficient Partitioning Strategies for PostgreSQL Table with KSUID and High Volume
Previous Message Arthur Bazin 2023-09-04 09:05:47 Re: pg_dump/pg_restore and the magic of the search_path