1

This is from the official documentation (http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html)

and two forms of CASE:

CASE ... WHEN ... THEN ... ELSE ... END CASE

CASE WHEN ... THEN ... ELSE ... END CASE

This doesn't work:

select case when 1 < 2 then 'a' else 'b' end case from pg_database limit 1;

It works with end instead of end case, though:

select case when 1 < 2 then 'a' else 'b' end from pg_database limit 1;

This is with PostgreSQL 9.4.6.

Why doesn't the syntax in the official docs match the syntax that the server apparently requires?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
clay
  • 18,138
  • 28
  • 107
  • 192

1 Answers1

3

You are confusing documentation for PL/pgSQL with the one for SQL. You link to the manual for PL/pgSQL, but your code displays an SQL expression.

Both use the key word CASE, but it's not the same thing. CASE in PL/pgSQL is a control structure for the procedural language, while CASE in SQL is a conditional expression.

You are not the first to be confused. There are other minor differences in the syntax. For instance, PL/pgSQL allows a list of values for the "simple CASE" variant, which is not possible in SQL:

... WHEN expression [, expression [ ... ]] THEN

Would be nice to have in SQL as well, but the standard doesn't allow it and Postgres sticks to the standard.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    Modern SQL allows list of expression in SQL `CASE` too. The procedural `CASE` is based on modern standard, SQL `CASE` is still ANSI SQL 99 compliant (missing optional feature F263). The reason, why SQL `CASE` missing this feature is simple - no body wrote it for Postgres. – Pavel Stehule Apr 18 '16 at 08:24
  • @PavelStehule: Oh, interesting, this might be low hanging froot for an ambitious developer. – Erwin Brandstetter Apr 18 '16 at 11:45