SQL PostgreSQL“IF”语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/802764/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL "IF" syntax error
提问by Strae
I'm new with PostgreSQL, and I already have my first problem..
我是 PostgreSQL 的新手，我已经遇到了我的第一个问题..
I wrote some code to understand how transactions work, following the manual step by step.
我写了一些代码来理解事务是如何工作的，按照手册一步一步来。
To make it short, I've created 2 tables, user and movements: in the first one there are the name, email and credit columns, in the second the columns from, to, import.
简而言之，我创建了 2 个表，用户和移动：在第一个中有 name、email 和 credit 列，在第二个中有 from、to、import 列。
So, I was trying this way:
所以，我是这样尝试的：
BEGIN;
INSERT INTO movements (from, to, import) VALUES ('mary', 'steve', 600);
UPDATE users SET credit = credit - 600 WHERE name = 'mary';
UPDATE users SET credit = credit + 600 WHERE name = 'steve';
--here comes the problem!
IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
ROLLBACK;
END IF
COMMIT;
I always get the error:
我总是收到错误：
ERROR: syntax error at or near "IF"
错误：“IF”处或附近的语法错误
Where am I mistaken?
我错在哪里？
P.S.: Don't focus on the example functionality, it's just a trial for me to understand the transactions.. and now, the IF clause...
PS：不要专注于示例功能，这只是我理解事务的尝试..现在，IF子句......
采纳答案by pyrocumulus
As Johannes already says: you are mixing regular SQL with PL/pgSQL, the stored procedure language. The link that Johannes provides should explain the concept of stored procedures to you.
正如约翰内斯所说：您将常规 SQL 与存储过程语言 PL/pgSQL 混合在一起。Johannes 提供的链接应该向您解释存储过程的概念。
I take it you're doing this as a script? Executing one statement after another? I'm afraid you can only do what you want to do inside a Stored Procedure, or Function, as you might call it. This is because when you are executing statements in this way, every statement stands on its own with no relation or information regarding the other statements.
我认为你是把它当成脚本来做的？一个接一个地执行语句？恐怕您只能在存储过程或函数中做您想做的事情，正如您所说的那样。这是因为当您以这种方式执行语句时，每个语句都独立存在，与其他语句没有任何关系或信息。
Furthermore you can look at the following link for more information on how to use IF ... THEN ... ELSE ... END IF; conditionals inside plpgsql: link.
此外，您可以查看以下链接，了解有关如何使用 IF ... THEN ... ELSE ... END IF; 的更多信息。plpgsql 中的条件：link。
EDIT:
编辑：
I don't know if ROLLBACK is allowed at that point (because each stored procedure is already in its own transaction), but you must be able to figure that out for yourself using the extensive documentation @ http://www.postgresql.org. Here's a sample function with your code in it, also demonstrating some other syntax:
我不知道此时是否允许 ROLLBACK（因为每个存储过程已经在它自己的事务中），但是您必须能够使用广泛的文档@ http://www.postgresql.org自己弄清楚这一点. 这是一个包含您的代码的示例函数，还演示了一些其他语法：
CREATE OR REPLACE FUNCTION public.test()
RETURNS integer AS
$$
DECLARE
tempvar integer;
BEGIN
tempvar := 1;
INSERT INTO movements (from, to, import) VALUES ('mary', 'steve', 600);
UPDATE users SET credit = credit - 600 WHERE name = 'mary';
UPDATE users SET credit = credit + 600 WHERE name = 'steve';
--here comes the problem!
IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
ROLLBACK;
END IF;
RETURN tempvar;
END
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;
However, if you are really going this road, I recommend using a GUI DB manager. It's easier for learning all this.
但是，如果您真的要走这条路，我建议您使用 GUI DB 管理器。学习这一切更容易。
回答by Johannes Weiss
回答by Adam Luter
If you want to avoid the if you could rewrite your code as:
如果您想避免 if 您可以将代码重写为：
BEGIN;
INSERT INTO movements (from, to, import)
SELECT 'mary', 'steve', CASE credit < 600 WHEN TRUE THEN 0 ELSE 600 END;
UPDATE users SET credit = credit - CASE credit < 600 WHEN TRUE THEN 0 ELSE 600 END
WHERE name = 'mary';
UPDATE users u SET u.credit = u.credit + CASE v.credit < 600 WHEN TRUE THEN 0 ELSE 600 END
FROM users v
WHERE u.name = 'steve' and v.name = 'mary'
COMMIT;
Yes, this is stupid :) .
是的，这很愚蠢:)。
回答by Ferry
You could try to modify the IF part, from:
您可以尝试修改 IF 部分，从：
IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
ROLLBACK;
END IF
to
到
SELECT SUM(credit) INTO v_credit FROM users WHERE name = 'mary';
IF (v_credit) < 0 THEN
ROLLBACK;
END IF
Assuming v_credit is a variable you defined previously. IMHO, Postgre assumes SELECT query returns more than one result, even though you're very certain that it's unique. So I think you could try to assign the value to a variable first beforehand.
假设 v_credit 是您之前定义的变量。恕我直言，Postgre 假设 SELECT 查询返回多个结果，即使您非常确定它是唯一的。所以我认为您可以尝试先将值分配给变量。
回答by MAbraham1
Similar to Microsoft's SQL and T/SQL, you should be able to mix regular SQL with PL/pgSQL if they are in the correct sequence. Here's an example where the sequence matters in a mixed SQL/PL stored proc:
与 Microsoft 的 SQL 和 T/SQL 类似，如果它们的顺序正确，您应该能够将常规 SQL 与 PL/pgSQL 混合使用。这是一个示例，其中序列在混合 SQL/PL 存储过程中很重要：
You cannot wrap conditional statements inside the cursor--you must put the cursor inside the conditional statement. If you do the sequence the other way around, you will get the same error as you'd seen, 'ERROR: syntax error at or near "IF"':
您不能将条件语句包装在游标内——您必须将游标置于条件语句内。如果您以相反的方式执行该序列，您将得到与您看到的相同的错误，“错误：“IF”处或附近的语法错误”：
CREATE OR REPLACE FUNCTION getSubsystemFaultListCount(_bunoid integer, _subsystem text, _starttime timestamp without time zone, _stoptime timestamp without time zone)
RETURNS refcursor AS
$BODY$
DECLARE mycurs refcursor;
BEGIN
IF _subsystem = 'ALL' THEN
OPEN mycurs FOR
SELECT count(*), fs_fault.faultcode, fs_fault.downloadtime
FROM fs_fault
WHERE fs_fault.bunoid = _bunoid
AND fs_fault.statusid IN(2, 4)
AND fs_fault.downloadtime BETWEEN _starttime AND _stoptime
GROUP BY fs_fault.faultcode, fs_fault.downloadtime;
RETURN mycurs;
ELSE
OPEN mycurs FOR
SELECT count(*), fs_fault.faultcode, fs_fault.downloadtime
FROM fs_fault
WHERE fs_fault.bunoid = _bunoid
AND fs_fault.subsystemid
IN(SELECT id FROM fs_subsystem WHERE type = _subsystem)
AND fs_fault.statusid IN(2, 4)
AND fs_fault.downloadtime BETWEEN _starttime AND _stoptime
GROUP BY fs_fault.faultcode, fs_fault.downloadtime;
RETURN mycurs;
END IF;
END;
$BODY$
I am a beginner in PostGresSQL; this function is just an example.
我是 PostGresSQL 的初学者；这个函数只是一个例子。
