CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Sai Wai Maung
Sai Wai Maung

Reputation: 1617

PostgreSQL - Syntax Error When Creating Function With If ELSIF Statements

Could someone help explain what's wrong with syntax for the following codes:

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;

I got the following error when tried to execute it.

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

Upvotes: 0

Views: 1259

Answers (3)

Greg
Greg

Reputation: 6759

Is this something you want to apply to the entire table tempoffset? I am probably missing something, but, as a_horse_with_no_name said, you could probably do this with an update followed by a delete, something like:

$BODY$
BEGIN

update tempoffset
set offset_factor = offset_factor *
   when tempoffset.ts_insert >= (now() at time zone 'utc') - '15 minutes'::interval
    AND tempoffset.ts_insert < (now() at time zone 'utc') - '30 minutes'::interval) then 0.75
   when tempoffset.ts_insert >= (now() at time zone 'utc') - '30 minutes'::interval
    AND tempoffset.ts_insert < (now() at time zone 'utc') - '45 minutes'::interval) then 0.50
   when tempoffset.ts_insert >= (now() at time zone 'utc') - '45 minutes'::interval
    AND tempoffset.ts_insert < (now() at time zone 'utc') - '60 minutes'::interval) then 0.25;

delete from tempoffset where tempoffset.ts_insert < (now() at time zone 'utc') - '15 minutes'::interval or
            tempoffset.ts_insert > (now() at time zone 'utc') - '60 minutes'::interval);


END;
$BODY$

Sorry, I didn't test it, I haven't used the when / then in a multiplication before. But, this will update the tempoffset table by the appropriate factors, or if the time is not bracketed then the data will be deleted. I felt like the <= > were bracketing backwards, but, maybe I got that wrong.

-g

Upvotes: 1

user330315
user330315

Reputation:

You can't use a table column like that in an if statement. You need to first retrieve the value of a row using select .. into and store it in a local variable. Then you can compare the values.

Something like:

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;

Upvotes: 2

dutchlab
dutchlab

Reputation: 590

Try removing the space between the '15 minutes':: interval in the if statement.

Upvotes: 0

Related Questions