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
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 |