How to use the ELSIF statement in Postgres

Question

I'm wondering if I can run the if statement by itself, or it can't stand on its own, has to be in a nested statement. I can't directly run the following code.

IF tax_year=2005 THEN
UPDATE table1 SET column1=column1*3;
ELSIF tax_year=2006 THEN
UPDATE table1 SET column1=column1*5;
ELSIF tax_year=2007 THEN
UPDATE table1 SET column1=column1*7;
END IF;

Also, I didn't write it out that when tax_year=2008, column1=column1. I'm not sure if it needs to be in the code since column1 won't change in 2008.

Thanks for your help!

Answer 1

IF / ELSIF / ELSE is part of PL/pgsql, which is an extension of pg, and it's enabled for new database by default.

You can create a function to wrap the IF statements. And call the function to execute these statements.

e.g

-- create function,
CREATE OR REPLACE FUNCTION fun_dummy_tmp(id_start integer, id_end integer) RETURNS setof dummy AS $$
DECLARE
BEGIN
    IF id_start <= 0 THEN
        id_start = 1;
    END IF;

    IF id_end < id_start THEN
        id_end = id_start;
    END IF;

    return query execute 'select * from dummy where id between $1 and $2' using id_start,id_end;

    return;
END;
$$ LANGUAGE plpgsql;

-- call function,
select * from fun_dummy_tmp(1, 4);

-- drop function,
DROP FUNCTION IF EXISTS fun_dummy_tmp(integer, integer);

And, there is a CASE statement, which might be a better choice for your requirement.

e.g

-- create function,
CREATE OR REPLACE FUNCTION fun_dummy_tmp(id integer) RETURNS varchar AS $$
DECLARE
    msg varchar;
BEGIN
    CASE id%2
        WHEN 0 THEN
            msg := 'even';

        WHEN 1 THEN
            msg := 'odd';
        ELSE
            msg := 'impossible';
    END CASE;

    return msg;
END;
$$ LANGUAGE plpgsql;

-- call function,
select * from fun_dummy_tmp(6);

-- drop function,
DROP FUNCTION IF EXISTS fun_dummy_tmp(integer);

You can refer to postgresql document for control statement for the details.