PostgreSQL, “ЕСЛИ” синтаксическая ошибка
Попробуйте:
tblTimes.Filter := 'start_time >= {dt 2019-02-01 00:00:00}';
tblTimes.Filtered := True;
и, пожалуйста, прочтите это: http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Preprocessing_Command_Text_ (FireDAC)
4 ответа
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.
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.
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:
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.
Вы можете попытаться изменить часть IF, с:
IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
ROLLBACK;
END IF
до
SELECT SUM(credit) INTO v_credit FROM users WHERE name = 'mary';
IF (v_credit) < 0 THEN
ROLLBACK;
END IF
Предполагая, что v_credit - это переменная, которую вы определили ранее. ИМХО, Postgre предполагает, что запрос SELECT возвращает более одного результата, даже если вы уверены, что он уникален. Поэтому я думаю, что вы могли бы попытаться заранее присвоить значение переменной.
If you want to avoid the if you could rewrite your code as:
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 :) .
Вы, кажется, используете простой SQL но оператор IF является частью процедурного языка PL / pgSQL , который является частью PostgreSQL.