CODEWITHSUNDEEP
sqlpostgresqlplpgsql
user1312885
user1312885

Reputation:

PostgreSQL IF-THEN-ELSE control structure

Why do I always get the following error from Postgres?

syntax error at or near "IF"

I read PostgreSQL: Documentation: 8.3: Control Structures. First I tried to execute a difficult query (with subquery), but then I tried to execute a simple one like this:

IF 2 <> 0 THEN select * from users; END IF;

The error is still the same. What am I doing wrong?

Upvotes: 10

Views: 56958

Answers (3)

Evandro Coan
Evandro Coan

Reputation: 9418

You're not enclosing that PL/pgSQL. They need to be enclosed with anonymous code block. Example for your code:

DO $$ BEGIN

    IF 2 <> 0 THEN select * from users; END IF;

END$$;

Upvotes: 1

Pavel Stehule
Pavel Stehule

Reputation: 45795

IF 2 <> 0 THEN select * from users; END IF;

You cannot use PL/pgSQL statements outside plpgsql functions. And if this fragment is from plpgsql function, then it is nonsense too. You cannot directly return result of query like T-SQL does. 

CREATE OR REPLACE FUNCTION test(p int)
RETURNS SETOF users AS $$
BEGIN
  IF p = 1 THEN
    RETURN QUERY SELECT * FROM users;
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;

When you would get some result from function, you have to use RETURN statement - plpgsql knows only function, it doesn't support procedures - so unbounded SELECT has not sense.

Upvotes: 18

Milen A. Radev
Milen A. Radev

Reputation: 62573

You're not enclosing that PL/pgSQL control structure in an anonymous block or a PL/pgSQL function.

For the SQL version of this control structure see the docs for CASE.

Upvotes: 5

Related Questions