sql - PostgreSQL - 使用 If ELSIF 语句创建函数时出现语法错误

Question

谁能帮忙解释一下以下代码的语法有什么问题:

CREATE OR REPLACE FUNCTION linearly_decrement_offset(location_in text)
RETURNS void AS
$BODY$BEGIN

IF tempoffset.ts_insert <= (now() at time zone 'utc') - '15 minutes':: interval AND tempoffset.ts_insert > (now() at time zone 'utc') - '30 minutes':: interval THEN
    UPDATE tempoffset
        SET offset_factor = offset_factor * 0.75
        WHERE tempoffset.location = location_in;
ELSIF tempoffset.ts_insert =< (now() at time zone 'utc') - '30 minutes'::interval AND tempoffset.ts_insert > (now() at time zone 'utc') - '45 minutes'::interval THEN
    UPDATE tempoffset
        SET offset_factor = offset_factor* 0.5
        WHERE tempoffset.location = location_in;
ELSIF tempoffset.ts_insert =< (now() at time zone 'utc') - '45 minutes'::interval AND tempoffset.ts_inset > (now() at time zone 'utc') - '1 hour'::interval THEN
    UPDATE tempoffset
        SET offset_factor = offset_factor * 0.25
        WHERE tempoffset.location = location_in;
ELSIF tempoffset.ts_insert < (now() at time zone 'utc') - '1 hour'::interval THEN
    DELETE FROM tempoffset;
END IF;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION linearly_decrement_offset(text)
OWNER TO postgres;

尝试执行时出现以下错误。

ERROR:  syntax error at or near "IF"
LINE 3:  IF tempoffset.ts_insert <= (now() at time zone 'utc') - '15...
         ^
********** Error **********

ERROR: syntax error at or near "IF"
SQL state: 42601
Character: 9

Answer 1

您不能在 if 语句中使用这样的表列。您需要先使用 select .. into 检索行的值并将其存储在局部变量中。然后您可以比较这些值。

类似于:

CREATE OR REPLACE FUNCTION linearly_decrement_offset(location_in text)
RETURNS void AS
$BODY$
declare
   l_insert_ts timestamp;  --- a local variable to hold the value
BEGIN

   -- this select assumes that location_in is unique 
   -- (and thus the select returns exactly one row)
   -- otherwise the select .. into will throw an error
   select ts_insert
      into l_insert_ts
   from tempoffset
   WHERE location = location_in;

    IF l_insert_ts <= (now() at time zone 'utc') - '15 minutes'::interval AND l_insert_ts > (now() at time zone 'utc') - '30 minutes'::interval THEN
        UPDATE tempoffset
            SET offset_factor = offset_factor * 0.75
            WHERE tempoffset.location = location_in;
    ELSIF l_insert_ts <= (now() at time zone 'utc') - '30 minutes'::interval AND l_insert_ts > (now() at time zone 'utc') - '45 minutes'::interval THEN
        UPDATE tempoffset
            SET offset_factor = offset_factor* 0.5
            WHERE tempoffset.location = location_in;
    ELSIF l_insert_ts <= (now() at time zone 'utc') - '45 minutes'::interval AND l_insert_ts > (now() at time zone 'utc') - '1 hour'::interval THEN
        UPDATE tempoffset
            SET offset_factor = offset_factor * 0.25
            WHERE tempoffset.location = location_in;
    ELSIF tempoffset.ts_insert < (now() at time zone 'utc') - '1 hour'::interval THEN
        DELETE FROM tempoffset;
    END IF;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;