Thread: bug with if ... then ... clause in views

While trying to create some views I stumbled on some
problem with using the if-then clause. Here is a
simple example :

CREATE OR REPLACE VIEW public.SomeView
  as select d.id,
  if (true) then d.DocNumber endif from
public.Z_Documents as d;

I get the following error :
 syntax error at or near "then" at character 72

I don't have a clue what is going on here. Any
suggestions?

Thanks in advance,
Emil

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Jan 18, 2006, at 18:18 , Emil Rachovsky wrote:

> CREATE OR REPLACE VIEW public.SomeView
>   as select d.id,
>   if (true) then d.DocNumber endif from
> public.Z_Documents as d;
>
> I get the following error :
>  syntax error at or near "then" at character 72

Well, one problem is that IF ... ENDIF is the correct syntax. You can
use it in PL/pgSQL, but that's a procedural language, and not what
you're doing here. You're probably looking for CASE, e.g.,

CREATE OR REPLACE VIEW public."SomeView" AS
SELECT d.id
    , CASE WHEN TRUE
        THEN d."DocNumber"
             ELSE something_else
             END as "DocNumber"
FROM public."Z_Documents" as d;

Note you need an ELSE clause: you can't have a variable number of
columns in the view (just like you can't have a variable number of
columns in a table). As an aside, you need to double-quote
identifiers if you want them to be case-sensitive: otherwise they'll
be down-cased.

Hope this helps.

Michael Glaesemann
grzm myrealbox com

Emil Rachovsky wrote:

>While trying to create some views I stumbled on some
>problem with using the if-then clause. Here is a
>simple example :
>
>CREATE OR REPLACE VIEW public.SomeView
>  as select d.id,
>  if (true) then d.DocNumber endif from
>public.Z_Documents as d;
>
>I get the following error :
> syntax error at or near "then" at character 72
>
>I don't have a clue what is going on here. Any
>suggestions?
>
>
>
IF / THEN is not part of SQL. it is part of plpgsql.
However you'll find that CASE can do the same thing.
select d.id, case when true then d.docNumber else 'something else' end
as blah, public.Z_documents as d ...

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/