sql - 使用 ROLLBACK TO SAVEPOINT(带条件)

Question

是否可以使用 CASE 来ROLLBACK TO SAVEPOINT？我的查询是

BEGIN;
SAVEPOINT my_savepoint;
INSERT INTO DPoint (uuid) VALUES ('5547f4b7-00b3-4aac-8ceb-c9ca163a0214')
ON CONFLICT (uuid) DO NOTHING;
WITH
 ins1 AS (INSERT INTO Point (latitude, longitude, srid)
VALUES (37.251667, 14.917222, 4326) RETURNING id),
 ins2 as (INSERT INTO SPoint (idPt, uuiddpt)
     VALUES ((SELECT id FROM ins1), '5547f4b7-00b3-4aac-8ceb-c9ca163a0214') RETURNING id),
 ins3 as (INSERT INTO Distance (idSpt, uuiddpt)
     VALUES ((SELECT id FROM ins2), '5547f4b7-00b3-4aac-8ceb-c9ca163a0214'))
INSERT INTO DPointTS (uuid, type, name, idPoint)
VALUES ('5547f4b7-00b3-4aac-8ceb-c9ca163a0214', NULL, NULL, (SELECT id FROM ins1));

SELECT CASE WHEN
(SELECT uuid FROM DPoint
WHERE uuid = '5547f4b7-00b3-4aac-8ceb-c9ca163a0214' )
is not NULL THEN ROLLBACK TO SAVEPOINT my_savepoint END;
COMMIT;

我的想法是:

当尝试再次插入 DPoint.uuid = '5547f4b7-00b3-4aac-8ceb-c9ca163a0214' 时，不需要插入 Point、SPoint、Distance、DPointTS。所以我想将这些插入回滚到事务中的 my_savepoint 中。也许知道我必须以什么方式重写我的代码？

编辑:

SELECT uuid IS NULL AS is_not_uuid FROM DPoint WHERE uuid = '5547f4b7-00b3-4aac-8ceb-c9ca163a0214';
\gset
\if :is_not_uuid
    \echo 'insert row to DPoint'
    INSERT INTO DPoint (uuid) VALUES ('5547f4b7-00b3-4aac-8ceb-c9ca163a0214');
    ... 
    my INSERT query

\endif

我在没有 SAVEPOINT 的情况下更新策略 - 如果 SELECT 查询返回 TRUE，我会评估所有插入。我如何仅在命令行中执行查询？当在 DataGRIP 中尝试使用 console.sql 时，它会抛出一个错误 - 它会诚实地执行所有行，并在 INSERT INTO DPoint (uuid)... 中失败，以防该点已经存在。我想以一种方式执行语句

Answer 1

不，你不能那样做。

您必须编写客户端代码并使用条件处理。

例如 psql :

-- set the variable "want_rollback" to TRUE or FALSE
SELECT uuid IS NOT NULL AS want_rollback
FROM dpoint
WHERE uuid = '5547f4b7-00b3-4aac-8ceb-c9ca163a0214' \gset
\if :want_rollback
ROLLBACK TO SAVEPOINT my_savepoint;
\endif

参见the documentation有关\if的详细信息:

\if <strong><em>expression</em></strong>
\elif <strong><em>expression</em></strong>
\else
\endif

This group of commands implements nestable conditional blocks. A conditional block must begin with an \if and end with an \endif. In between there may be any number of \elif clauses, which may optionally be followed by a single \else clause. Ordinary queries and other types of backslash commands may (and usually do) appear between the commands forming a conditional block.

The \if and \elif commands read their argument(s) and evaluate them as a boolean expression. If the expression yields true then processing continues normally; otherwise, lines are skipped until a matching \elif, \else, or \endif is reached. Once an \if or \elif test has succeeded, the arguments of later \elif commands in the same block are not evaluated but are treated as false. Lines following an \else are processed only if no earlier matching \if or \elif succeeded.

The <strong><em>expression</em></strong> argument of an \if or \elif command is subject to variable interpolation and backquote expansion, just like any other backslash command argument. After that it is evaluated like the value of an on/off option variable. So a valid value is any unambiguous case-insensitive match for one of: true, false, 1, 0, on, off, yes, no. For example, t, T, and tR will all be considered to be true.

Expressions that do not properly evaluate to true or false will generate a warning and be treated as false.

Lines being skipped are parsed normally to identify queries and backslash commands, but queries are not sent to the server, and backslash commands other than conditionals (\if, \elif, \else, \endif) are ignored. Conditional commands are checked only for valid nesting. Variable references in skipped lines are not expanded, and backquote expansion is not performed either.

All the backslash commands of a given conditional block must appear in the same source file. If EOF is reached on the main input file or an \include-ed file before all local \if-blocks have been closed, then psql will raise an error.

同一页面还将解释\gset .