PostgreSQL - Syntax Error When Creating Function With If ELSIF Statements

Question

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

Just a note, you could probably simplify this using something like 1 - width_bucket(...)*0.25. width_bucket doc. Also not quite sure why you want to store this in a temp table instead of calculating on the fly. — jpmc26
– jpmc26, Commented Sep 8, 2014 at 18:50
You can't use the column of a table in an IF statement like that. To which row would that belong to? You need to run a select query to retrieve data from a table. Although this looks like as if the you don't need an IF at all. This could probably be done in a single update statement (apart from the delete). What exactly are you trying to achieve with that? — user330315
– user330315, Commented Sep 8, 2014 at 19:05
@a_horse_with_no_name I am trying to linearly decrease the offset_factor value from tempoffset table. So, for example decrease the offset_factor value by 25% if the existing values are 15 minutes and older but less than 30 mintues from the current query, and so on. I am trying to implement a REST API for an iOS app. — Sai Wai Maung
– Sai Wai Maung, Commented Sep 8, 2014 at 19:12
Is this something that really needs to be done server side? Are you using this for something server side or is the client actually fetching it? If the client is actually fetching it directly, you could save yourself the network overhead by doing it client side. Even server side, if it's not something you need in database, you could save yourself the overhead of fetching from the DB by calculating in the app code. — jpmc26
– jpmc26, Commented Sep 8, 2014 at 19:15

score 2 · Accepted Answer · 2014-09-08 19:27:25Z

2

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;

edited Sep 8, 2014 at 19:27

answered Sep 8, 2014 at 19:18

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

@SaiWaiMaung then you need to take a step back and define the problem you're trying to solve better. The problem isn't just a syntax error. Describe what you're actually trying to do, what the data is, how you're trying to transform it, etc.

Greg · Accepted Answer · 2014-09-08 19:36:41Z

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

dutchlab · Accepted Answer · 2014-09-08 19:02:25Z

0

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

answered Sep 8, 2014 at 19:02

dutchlab

6002 gold badges13 silver badges29 bronze badges

5 Comments

dutchlab Over a year ago

The space between :: and interval.

dutchlab Over a year ago

I just want to verify that you realize that in your comment you have no space between the ::interval. However the code you supplied has a space on the 15 minute line and the others do not.

user330315 Over a year ago

Ah I didn't see that because you didn't format the example as code. But still: the space doesn't make a difference. '15 minutes':: interval' works just as fine as '15 minutes'::interval

dutchlab Over a year ago

try switching the first <= to =<

user330315 Over a year ago

That's wrong as well. the "lower or equal" operator in SQL and PL/pgSQL is <= not =<

Collectives™ on Stack Overflow

PostgreSQL - Syntax Error When Creating Function With If ELSIF Statements

3 Answers 3

1 Comment

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related