proposal for 8.4: PL/pgSQL - statement CASE

Hello

I found so PL/SQL support CASE statement
http://download-east.oracle.com/docs/cd/B10500_01/appdev.920/a96624/04_struc.htm#484

I propose add this statement to PL/pgSQL too.

Reasons:

a) it's useful construct,
b) this statement is defined in SQL/PSM - better conformance with ANSI

Implementation:
This statement is implemented in PL/pgPSM, so implementation will be
backported to PL/pgSQL.

Any ideas?

Regards
Pavel Stehule

Pavel,

> I propose add this statement to PL/pgSQL too.

Isn't there a danger of syntactical conflict with the SQL SELECT ... CASE
statement?

I'd love to have CASE in PL/pgSQL, but I always thought that stood in the way.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

Josh Berkus wrote:
> Pavel,
>
>
>> I propose add this statement to PL/pgSQL too.
>>
>
> Isn't there a danger of syntactical conflict with the SQL SELECT ... CASE
> statement?
>
> I'd love to have CASE in PL/pgSQL, but I always thought that stood in the way.
>

It should be possible to disambiguate them, I believe.

You might find that a CASE statement does a little less than you expect,
though, if like the rest of PL/pgSQL it follows Ada rules.

cheers

andrew

Josh Berkus wrote:
> Pavel,
>
>> I propose add this statement to PL/pgSQL too.
>
> Isn't there a danger of syntactical conflict with the SQL SELECT ... CASE
> statement?
>
> I'd love to have CASE in PL/pgSQL, but I always thought that stood in the way.
>

Could it be called SWITCH instead?

Joshua D. Drake

Joshua D. Drake wrote:
> Josh Berkus wrote:
>> Pavel,
>>
>>> I propose add this statement to PL/pgSQL too.
>>
>> Isn't there a danger of syntactical conflict with the SQL SELECT ...
>> CASE statement?
>>
>> I'd love to have CASE in PL/pgSQL, but I always thought that stood in
>> the way.
>>
>
> Could it be called SWITCH instead?
>
>

That would surely defeat the whole point of having this. We want to have
the same syntax as PL/SQL, not different syntax for the same things.

cheers

andrew

On Thu, 2008-01-17 at 16:01 +0100, Pavel Stehule wrote:

> I found so PL/SQL support CASE statement
> http://download-east.oracle.com/docs/cd/B10500_01/appdev.920/a96624/04_struc.htm#484
>
> I propose add this statement to PL/pgSQL too.

Please don't post links to potentially copyrighted works.

We don't want things just because Oracle has them, we want them for a
real reason. If say, you had a migration project that would be made
easier by that feature, or there was clear benefit in improving the
language through the addition of a feature, maybe. If not, we must try
to do something better than everybody else, not just the same, unless
its an official standard.

I'd be interested in hearing about how SQL/PSM is going. What are your
plans for that?

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Hello

>
> Isn't there a danger of syntactical conflict with the SQL SELECT ... CASE
> statement?
>

no, isn't. SELECT CASE can be only in expression .. inside SQL
statement, but PL/SQL CASE is PL statement. These are two different
worlds. SELECT CASE is invisible for pl parser, because pl parser
solves statements, not expressions.

> I'd love to have CASE in PL/pgSQL, but I always thought that stood in the way.
>

it's safe. SQL/PSM use it too.

Pavel

On 17/01/2008, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> Josh Berkus wrote:
> > Pavel,
> >
> >> I propose add this statement to PL/pgSQL too.
> >
> > Isn't there a danger of syntactical conflict with the SQL SELECT ... CASE
> > statement?
> >
> > I'd love to have CASE in PL/pgSQL, but I always thought that stood in the way.
> >
>
> Could it be called SWITCH instead?
>
I unlike it. There isn't reason why don't implement ANSI SQL standard construct.

Pavel

> Joshua D. Drake
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 17 Jan 2008 11:11:42 -0500
Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> That would surely defeat the whole point of having this. We want to
> have the same syntax as PL/SQL, not different syntax for the same
> things.

I'm sorry I thought we were developing PostgreSQL.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHj4RlATb/zqfZUUQRAmidAKCsRmGkQezCs+KqXOQ860V9g4jDnwCgkIKS
9qdY+ULKlRMucvxideWzjQE=
=REm+
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 17 Jan 2008 16:18:56 +0000
Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On Thu, 2008-01-17 at 16:01 +0100, Pavel Stehule wrote:
>
> > I found so PL/SQL support CASE statement
> > http://download-east.oracle.com/docs/cd/B10500_01/appdev.920/a96624/04_struc.htm#484
> >
> > I propose add this statement to PL/pgSQL too.
>
> Please don't post links to potentially copyrighted works.

Simon, this email is copyrighted... ;)

>
> We don't want things just because Oracle has them, we want them for a
> real reason. If say, you had a migration project that would be made
> easier by that feature, or there was clear benefit in improving the
> language through the addition of a feature, maybe. If not, we must try
> to do something better than everybody else, not just the same, unless
> its an official standard.

Exactly.

>
> I'd be interested in hearing about how SQL/PSM is going. What are your
> plans for that?
>

That is certainly more interesting to me as well.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHj4SRATb/zqfZUUQRAudoAJ0aw+35NlV9DLy1wwBRtlehMkw+BQCfbYJj
LtbfdSnbIEPiMBFnCgXEFW4=
=+Y+Y
-----END PGP SIGNATURE-----

On 17/01/2008, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Thu, 2008-01-17 at 16:01 +0100, Pavel Stehule wrote:
>
> > I found so PL/SQL support CASE statement
> > http://download-east.oracle.com/docs/cd/B10500_01/appdev.920/a96624/04_struc.htm#484
> >
> > I propose add this statement to PL/pgSQL too.
>
> Please don't post links to potentially copyrighted works.

I am sorry.
correct link http://www.pgsql.cz/index.php/Condition_statements
>
> We don't want things just because Oracle has them, we want them for a
> real reason. If say, you had a migration project that would be made
> easier by that feature, or there was clear benefit in improving the
> language through the addition of a feature, maybe. If not, we must try
> to do something better than everybody else, not just the same, unless
> its an official standard.

I understand well. But I would to respect similarity with PL/SQL. I
don't see reason for new special language for stored procedures that
is available only on PostgreSQL. And in this case Oracle PL/SQL CASE
statement is implementation of SQL/PSM CASE statement. Still SQL/PSM
isn't main language in PostgreSQL and isn't reason for fixation
plpgsql.

>
> I'd be interested in hearing about how SQL/PSM is going. What are your
> plans for that?
>

I prepare rpm and sources for 8.3 available from my archive and from
Devrim repository. There still some unsupported points - PostgreSQL
doesn't support all OOP features and some but not important things
depend on it (but I am not able to implement 100% clean SQL/PSM). And
than I will wait. Queue is full and I see lot of things with higher
priority than is SQL/PSM. I would to see real stored procedures in
8.4, but I don't belive.

Pavel

> --
> Simon Riggs
> 2ndQuadrant http://www.2ndQuadrant.com
>
>

Joshua D. Drake wrote:
> On Thu, 17 Jan 2008 11:11:42 -0500
> Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
>> That would surely defeat the whole point of having this. We want to
>> have the same syntax as PL/SQL, not different syntax for the same
>> things.
>>
>
> I'm sorry I thought we were developing PostgreSQL.
>
>

Certainly. But that doesn't mean we should wantonly introduce
incompatibilities with PL/SQL. We have in the past rejected syntax
changes which would have done so - this would hardly be the first time.

In any case, as Pavel points out, Josh's original suggestion that there
would be some ambiguity is wrong, so the discussion seems to me to be moot.

cheers

andrew

>
> I'm sorry I thought we were developing PostgreSQL.
>

we develop PostgreSQL, but why create own syntax for all? Why? Only so
we develop PostgreSQL? We have different implementation and different
limit, but why create different syntax, I don't understand. It's like
Microsoft. Lot of things are little bit incompatible.

Pavel

> Sincerely,
>
> Joshua D. Drake
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 17 Jan 2008 17:43:45 +0100
"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> wrote:

> we develop PostgreSQL, but why create own syntax for all? Why? Only so
> we develop PostgreSQL? We have different implementation and different
> limit, but why create different syntax, I don't understand. It's like
> Microsoft. Lot of things are little bit incompatible.

O.k. hold on guys. I really don't care what you do with plpgsql. I
don't use it unless I absolutely have to anyway. All I was doing was
saying that:

A. I could give flying donkey butt about being the Oracle-Compatible
community.

B. That "SWITCH" may be an alternate syntax because 15 years ago when I
took a CS class and I did one chapter of C they had a SWITCH statement
that resembles CASE.

Please continue on.

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHj4hVATb/zqfZUUQRAhbpAJ9+qwBAHqxwSCCeVzbRjKBiFTpVhQCdEX+j
vrXiUgBeLQoBKx3s68214qw=
=kc7c
-----END PGP SIGNATURE-----

>
> A. I could give flying donkey butt about being the Oracle-Compatible
> community.
>
> B. That "SWITCH" may be an alternate syntax because 15 years ago when I
> took a CS class and I did one chapter of C they had a SWITCH statement
> that resembles CASE.
>

Primary goal is ANSI SQL conformance (for me). Current PL/pgSQL isn't
compatible and it will not be compatible in future (we have different
implementation of SRF and really specific implementation of OUT
parameters). But why artificially create bigger dif between PL/pgSQL
and PL/SQL?

I am sorry, I can't to speak in English gently (because my English is
all else than English), and some my notes are maybe too much hard.

Pavel

> Please continue on.
>
> Sincerely,
>
> Joshua D. Drake
>
> - --
> The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
> Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
> SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFHj4hVATb/zqfZUUQRAhbpAJ9+qwBAHqxwSCCeVzbRjKBiFTpVhQCdEX+j
> vrXiUgBeLQoBKx3s68214qw=
> =kc7c
> -----END PGP SIGNATURE-----
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 17 Jan 2008 18:00:21 +0100
"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> wrote:

> Primary goal is ANSI SQL conformance (for me). Current PL/pgSQL isn't
> compatible and it will not be compatible in future (we have different
> implementation of SRF and really specific implementation of OUT
> parameters). But why artificially create bigger dif between PL/pgSQL
> and PL/SQL?
>
> I am sorry, I can't to speak in English gently (because my English is
> all else than English), and some my notes are maybe too much hard.

If primary goal is ANSI SQL conformance shouldn't we be focusing on
pl/psm not plpgsql? (yes I am aware they are similar syntatically)

Sincerely,

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHj4udATb/zqfZUUQRAgyeAJ9Cb9pAMiWvP/JDv6F89JPBAh2IPACeI6a6
0yl+dXdE9XyCEoGNCeb9EXw=
=oHVb
-----END PGP SIGNATURE-----

On Jan 17, 2008 12:08 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> > Primary goal is ANSI SQL conformance (for me). Current PL/pgSQL isn't
> > compatible and it will not be compatible in future (we have different
> > implementation of SRF and really specific implementation of OUT
> > parameters). But why artificially create bigger dif between PL/pgSQL
> > and PL/SQL?
> >
> > I am sorry, I can't to speak in English gently (because my English is
> > all else than English), and some my notes are maybe too much hard.
>
> If primary goal is ANSI SQL conformance shouldn't we be focusing on
> pl/psm not plpgsql? (yes I am aware they are similar syntatically)

ANSI SQL conformance is not necessarily the only goal. Being able to
easily port Oracle applications is pretty nice. Being able to run
T-SQL in some fashion would be nice as well.

merlin

Joshua D. Drake wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Thu, 17 Jan 2008 17:43:45 +0100
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
> > we develop PostgreSQL, but why create own syntax for all? Why? Only so
> > we develop PostgreSQL? We have different implementation and different
> > limit, but why create different syntax, I don't understand. It's like
> > Microsoft. Lot of things are little bit incompatible.
>
> O.k. hold on guys. I really don't care what you do with plpgsql. I
> don't use it unless I absolutely have to anyway. All I was doing was
> saying that:
>
> A. I could give flying donkey butt about being the Oracle-Compatible
> community.

Well, our standard process is to check the ANSI syntax and if the
feature we want isn't mentioned we look at how Oracle or other databases
do it for ideas. I don't see why that should change.

pl/PgSQL already is aimed at being Oracle compatible so why complain
that the author wants to use Oracle syntax if possible. If you don't
care, that is fine, but as a project we do, at least in helping people
migrate to Postgres from other databases.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 17 Jan 2008 12:44:41 -0500 (EST)
Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> pl/PgSQL already is aimed at being Oracle compatible so why complain
> that the author wants to use Oracle syntax if possible. If you don't
> care, that is fine, but as a project we do, at least in helping people
> migrate to Postgres from other databases.
>

This appears to be the, "can't follow written points day". Magnus has
already given up on a thread on advocacy. I give up here.

Joshua D. Drake

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHj5dmATb/zqfZUUQRAgtDAJ9H8igJII+kyAxarEFwtPMhe3GG+ACeLOjq
p9MZzOrDM2z3nDGw2H2c1Og=
=UnFv
-----END PGP SIGNATURE-----

> If primary goal is ANSI SQL conformance shouldn't we be focusing on
> pl/psm not plpgsql? (yes I am aware they are similar syntatically)

I am not sure if in 8.4 will be space for changes in PL interprets. I
expect so plpgsql will be main SQL language next two years. I don't
plan any big changes - some cases like this is different (it's 2days
work).

Pavel

On Jan 18, 2008 3:19 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> > Isn't there a danger of syntactical conflict with the SQL SELECT ... CASE
> > statement?
>
> no, isn't. SELECT CASE can be only in expression .. inside SQL
> statement, but PL/SQL CASE is PL statement. These are two different
> worlds. SELECT CASE is invisible for pl parser, because pl parser
> solves statements, not expressions.
>

Sounds good. Chaining ELSIFs is ugly; I've wished for something like
this in PL/pgSQL from time to time while writing my functions.

A hearty "+1" from me. Let me know if there's anything I can do to
help move it forward.

Cheers
BJ

Pavel,

Speaking as someone who does lots of PL/pgSQL, CASE would be *great*.
Especially for triggers.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Added to TODO:

o Add CASE capability to language (already in SQL)

http://archives.postgresql.org/pgsql-hackers/2008-01/msg00696.php

---------------------------------------------------------------------------

Pavel Stehule wrote:
> Hello
>
> I found so PL/SQL support CASE statement
> http://download-east.oracle.com/docs/cd/B10500_01/appdev.920/a96624/04_struc.htm#484
>
> I propose add this statement to PL/pgSQL too.
>
> Reasons:
>
> a) it's useful construct,
> b) this statement is defined in SQL/PSM - better conformance with ANSI
>
> Implementation:
> This statement is implemented in PL/pgPSM, so implementation will be
> backported to PL/pgSQL.
>
> Any ideas?
>
> Regards
> Pavel Stehule
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +