pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbug with if ... then ... clause in views
4+ messages / 4 participants
[nested] [flat]
* bug with if ... then ... clause in views
@ 2006-01-18 09:18 Emil Rachovsky <zsevgymko@yahoo.com>
2006-01-18 09:28 ` Re: [SQL] bug with if ... then ... clause in views Michael Glaesemann <grzm@myrealbox.com>
2006-01-18 12:32 ` Re: bug with if ... then ... clause in views Daryl Richter <daryl@eddl.us>
2006-01-18 15:41 ` Re: bug with if ... then ... clause in views Jeff <threshar@torgo.978.org>
0 siblings, 3 replies; 4+ messages in thread
From: Emil Rachovsky @ 2006-01-18 09:18 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] bug with if ... then ... clause in views
2006-01-18 09:18 bug with if ... then ... clause in views Emil Rachovsky <zsevgymko@yahoo.com>
@ 2006-01-18 09:28 ` Michael Glaesemann <grzm@myrealbox.com>
2 siblings, 0 replies; 4+ messages in thread
From: Michael Glaesemann @ 2006-01-18 09:28 UTC (permalink / raw)
To: Emil Rachovsky <zsevgymko@yahoo.com>; +Cc: pgsql-sql; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: bug with if ... then ... clause in views
2006-01-18 09:18 bug with if ... then ... clause in views Emil Rachovsky <zsevgymko@yahoo.com>
@ 2006-01-18 12:32 ` Daryl Richter <daryl@eddl.us>
2 siblings, 0 replies; 4+ messages in thread
From: Daryl Richter @ 2006-01-18 12:32 UTC (permalink / raw)
To: pgsql-sql
On Jan 18, 2006, at 4:18 AM, 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?
I see 2 issues.
1) if *what* is true?
2) AFAIK, there is no IF conditional in SQL. Perhaps you want CASE?
e.g.
CREATE OR REPLACE VIEW SomeView
as
select
d.id,
case
when condition = true then d.doc_number
else 'Bad Doc'
end
from
documents as d;
go
>
> Thanks in advance,
> Emil
>
--
Daryl
"Stress rarely has a positive impact on our ability to think.
Never, I'd guess."
-- Ron Jeffries, 2005
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: bug with if ... then ... clause in views
2006-01-18 09:18 bug with if ... then ... clause in views Emil Rachovsky <zsevgymko@yahoo.com>
@ 2006-01-18 15:41 ` Jeff <threshar@torgo.978.org>
2 siblings, 0 replies; 4+ messages in thread
From: Jeff @ 2006-01-18 15:41 UTC (permalink / raw)
To: Emil Rachovsky <zsevgymko@yahoo.com>; +Cc: pgsql-sql; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-01-18 15:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-18 09:18 bug with if ... then ... clause in views Emil Rachovsky <zsevgymko@yahoo.com>
2006-01-18 09:28 ` Michael Glaesemann <grzm@myrealbox.com>
2006-01-18 12:32 ` Daryl Richter <daryl@eddl.us>
2006-01-18 15:41 ` Jeff <threshar@torgo.978.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox