Re: [SQL] bug with if ... then ... clause in views

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