Thread: 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@commandprompt.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@dunslane.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@2ndquadrant.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@2ndquadrant.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@dunslane.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.stehule@gmail.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.stehule@gmail.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@commandprompt.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.stehule@gmail.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@momjian.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@momjian.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.stehule@gmail.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@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +