用法有条件时回滚到SAVEPOINT [英] Usage ROLLBACK TO SAVEPOINT with condition

问题描述

是否可以使用CASE进行ROLLBACK TO SAVEPOINT? 我的查询是

Is it possible to ROLLBACK TO SAVEPOINT with CASE? My query is

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;

我的想法是:

My idea is:

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

When trying to insert once again DPoint.uuid = '5547f4b7-00b3-4aac-8ceb-c9ca163a0214', it is no need to insert Point, SPoint, Distance, DPointTS. So I would like to ROLLBACK these insertions to my_savepoint in transaction. Maybe any idea in what way I have to rewrite my code?

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

我更新了没有SAVEPOINTs的策略-如果SELECT查询返回TRUE，我会评估所有插入.我只能通过命令行执行查询的方式是什么?在DataGRIP中的console.sql中尝试时，将引发错误-诚实地执行所有行，如果该点已经存在，则在INSERT INTO DPoint (uuid)...中失败.我想以一种方式执行语句

I update my strategy without SAVEPOINTs - if SELECT query returns TRUE I evaluate all insertions. What way I am execute the query, only in command line? When trying in console.sql in DataGRIP it throws an error - it honestly execute all the rows and fails in INSERT INTO DPoint (uuid)... in case the point is already exists. I would like to execute the statements in one way

推荐答案

不，您不能那样做.

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

You will have to write client code and use conditional processing.

例如使用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

有关\if的详细信息，请参见文档./p>

See the documentation for details about \if:

\if expression
\elif expression
\else
\endif

\if expression
\elif expression
\else
\endif

这组命令实现了可嵌套的条件块.条件块必须以\if开头，并以\endif结尾.在两者之间可以有任意数量的\elif子句，可以选择在其后跟随单个\else子句.普通查询和其他类型的反斜杠命令可能会(并且通常会出现在命令之间)形成条件块.

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.

\if和\elif命令读取其自变量，并将其评估为布尔表达式.如果表达式的结果为true，则处理将正常继续；否则，处理将继续进行.否则，将跳过行，直到达到匹配的\elif，\else或\endif.一旦成功完成了\if或\elif的测试，就不会对同一块中更高版本的\elif命令的参数进行求值，而是将其视为false.仅在没有成功匹配\if或\elif的情况下，才处理\else之后的行.

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.

\if或\elif命令的expression自变量与其他任何反斜杠命令自变量一样，都经过变量插值和反引号扩展.之后，将像打开/关闭选项变量的值一样对它进行评估.因此，有效值是对true，false，1，0，on，off，yes，no之一的任何明确的不区分大小写的匹配.例如，t，T和tR都将被视为true.

The expression 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.

未正确评估为true或false的表达式将生成警告，并被视为false.

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

通常会分析被跳过的行以标识查询和反斜杠命令，但是查询不会发送到服务器，并且除条件语句(\if，\elif，\else，\endif)之外的反斜杠命令都将被忽略.仅检查条件命令的有效嵌套.跳过行中的变量引用不会扩展，也不会执行反引号扩展.

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.

给定条件块的所有反斜杠命令必须出现在同一源文件中.如果在所有本地\ if-blocks关闭之前在主输入文件或\ include-ed文件上达到EOF，则psql将引发错误.

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.

这篇关于用法有条件时回滚到SAVEPOINT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！