Thread: Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)
Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)
From
Pavel Stehule
Date:
Hello
This patch contains three oracle users missing functions. But I
hope can be usefull for all PostgreSQL users (users vote me ;-) Certainly
LEAST and GREATEST, which has not analogy. Using of DECODE is similar
CASE, but with some differences. There exist some workarounds in plpgsql,
but are ugly and neefective, or impossible (function DECODE rotate type of
args). All functions share code.
David, please, can you enhance documentation?
pokus=# select least(1,2,3,4);
least
-------
1
(1 row)
pokus=# select greatest(1,2,3,4);
greatest
----------
4
(1 row)
pokus=# select decode('c','a',2,1);
decode
--------
1
Best regards
Pavel Stehule
Attachment
This patch looks good. I know greater/least are seen as valuable, but
do we want decode()? It seems OK to me but I thought there was concern
about it in the past because it duplicated some existing functionality.
---------------------------------------------------------------------------
Pavel Stehule wrote:
> Hello
>
> This patch contains three oracle users missing functions. But I
> hope can be usefull for all PostgreSQL users (users vote me ;-) Certainly
> LEAST and GREATEST, which has not analogy. Using of DECODE is similar
> CASE, but with some differences. There exist some workarounds in plpgsql,
> but are ugly and neefective, or impossible (function DECODE rotate type of
> args). All functions share code.
>
> David, please, can you enhance documentation?
>
> pokus=# select least(1,2,3,4);
> least
> -------
> 1
> (1 row)
>
> pokus=# select greatest(1,2,3,4);
> greatest
> ----------
> 4
> (1 row)
>
> pokus=# select decode('c','a',2,1);
> decode
> --------
> 1
>
> Best regards
> Pavel Stehule
>
>
>
Content-Description:
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Hello, I sended version with only LEAST and GREATEST http://archives.postgresql.org/pgsql-patches/2005-06/msg00185.php this patch contains diff files for NEXT_DAY and LAST_DAY function too, which I think are generally usefull. There is duplicity with CASE. Regards Pavel On Tue, 21 Jun 2005, Bruce Momjian wrote: > > This patch looks good. I know greater/least are seen as valuable, but > do we want decode()? It seems OK to me but I thought there was concern > about it in the past because it duplicated some existing functionality. > > --------------------------------------------------------------------------- > > Pavel Stehule wrote: > > Hello > > > > This patch contains three oracle users missing functions. But I > > hope can be usefull for all PostgreSQL users (users vote me ;-) Certainly > > LEAST and GREATEST, which has not analogy. Using of DECODE is similar > > CASE, but with some differences. There exist some workarounds in plpgsql, > > but are ugly and neefective, or impossible (function DECODE rotate type of > > args). All functions share code. > > > > David, please, can you enhance documentation? > > > > pokus=# select least(1,2,3,4); > > least > > ------- > > 1 > > (1 row) > > > > pokus=# select greatest(1,2,3,4); > > greatest > > ---------- > > 4 > > (1 row) > > > > pokus=# select decode('c','a',2,1); > > decode > > -------- > > 1 > > > > Best regards > > Pavel Stehule > > > > > > > > Content-Description: > > [ Attachment, skipping... ] > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > >
Pavel Stehule wrote: > Hello, > > I sended version with only LEAST and GREATEST > http://archives.postgresql.org/pgsql-patches/2005-06/msg00185.php OK, did you remove DECODE for a reason? > this patch contains diff files for NEXT_DAY and LAST_DAY function too, > which I think are generally usefull. > > There is duplicity with CASE. I didn't think we needed NEXT_DAY and LAST_DAY because we have + interval like + '1 day'. We decided that, right? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Pavel Stehule wrote: > Hello, > > I sended version with only LEAST and GREATEST > http://archives.postgresql.org/pgsql-patches/2005-06/msg00185.php > > this patch contains diff files for NEXT_DAY and LAST_DAY function too, > which I think are generally usefull. > > There is duplicity with CASE. Oh, and I personally think DECODE is fine, even if it partly duplicates something we already have. I was just asking to make sure everyone else was OK before I applied it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Oh, and I personally think DECODE is fine, even if it partly duplicates
> something we already have. I was just asking to make sure everyone else
> was OK before I applied it.
I would rather not have a useless variant spelling of CASE ...
LEAST/GREATEST at least do something that's a bit hard to do otherwise.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Oh, and I personally think DECODE is fine, even if it partly duplicates > > something we already have. I was just asking to make sure everyone else > > was OK before I applied it. > > I would rather not have a useless variant spelling of CASE ... > LEAST/GREATEST at least do something that's a bit hard to do otherwise. OK, I will apply the LEAST/GREATEST parts and see if others can argue for decode(). -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, I will apply the LEAST/GREATEST parts and see if others can argue
> for decode().
Actually, I'd like to review the patch before it goes in --- if anyone's
looked at the code, I saw no comments about it ...
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > OK, I will apply the LEAST/GREATEST parts and see if others can argue > > for decode(). > > Actually, I'd like to review the patch before it goes in --- if anyone's > looked at the code, I saw no comments about it ... No, no one commented. I wanted to improve the SGML documentation a little, but feel free to adjust and apply. It is in the patch queue. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Tue, 21 Jun 2005, Bruce Momjian wrote: > Pavel Stehule wrote: > > Hello, > > > > I sended version with only LEAST and GREATEST > > http://archives.postgresql.org/pgsql-patches/2005-06/msg00185.php > > OK, did you remove DECODE for a reason? > o simplify patch o decode can be added in future if will be requirement > > this patch contains diff files for NEXT_DAY and LAST_DAY function too, > > which I think are generally usefull. > > > > There is duplicity with CASE. > > I didn't think we needed NEXT_DAY and LAST_DAY because we have + > interval like + '1 day'. We decided that, right? > next_day not equal date + 1day, but like next monday, ... look to doc. next_day (date, varchar) -- varchar = sunday|monday|... last_day is very often used function. Really. I can substitute create or replace function last_day(date) returns date $$ select date_trunc('month', $1 + interval '1month') - 1; $$ language sql; but I think can be usefull (for first day we have date_drunc, for last_date nothing) regards Pavel
Pavel Stehule <stehule@kix.fsv.cvut.cz> writes: + /* If any argument is null, then result is null (for GREATEST and LEAST)*/ Are you sure about that? The only reference I could find says that these functions are not strict in Oracle: http://download-east.oracle.com/otn_hosted_doc/rdb/pdf/sql_ref_v71_vol1.pdf on page 2-185: The NULL keyword can appear in the list but is ignored. However, not all value expressions can be specified as NULL. That is, a non-NULL value expression must be in the list so that the data type for the expression can be determined. The GREATEST and LEAST functions can result in NULL only if at run time all value expressions result in NULL. The strict interpretation is mathematically cleaner, no doubt, but offhand it seems less useful. regards, tom lane
On Thu, 23 Jun 2005, Tom Lane wrote: > Pavel Stehule <stehule@kix.fsv.cvut.cz> writes: > + /* If any argument is null, then result is null (for GREATEST and LEAST)*/ > > Are you sure about that? The only reference I could find says that > these functions are not strict in Oracle: > > http://download-east.oracle.com/otn_hosted_doc/rdb/pdf/sql_ref_v71_vol1.pdf > on page 2-185: > > The NULL keyword can appear in the list but is ignored. However, not all > value expressions can be specified as NULL. That is, a non-NULL value > expression must be in the list so that the data type for the expression > can be determined. > The GREATEST and LEAST functions can result in NULL only if at run time > all value expressions result in NULL. > > The strict interpretation is mathematically cleaner, no doubt, but > offhand it seems less useful. > I know it, But when moustly PostgreSQL function is strict I desided so greatest and least will be strict. There is two analogy: one, normal comparing which implicate strinct aggregate function which ignore NULL. what I have to chose? For compatibility there isn't biggeer changes. Only //if (*isNull) // return value; if (result && *isNull == false) { locfcinfo.arg[0] = result; ... } ----- foreach(arg, ..) { if (IsA(e, Const)) if (!((Const *) e)->constisnull) newargs = lappend(newargs, e); } if (newargs == NULL) return (Node *) makeNullConst(varargexpr->..); ----- Tom I don't know, what is better. Maybe Oracle, because least(nullif(col2, +max), nullif(col2, +max)) isn't really readable, but it's "precedens" for PostgreSQL. I selected more conservative solution, but my patches are only start points for discussion (really) :). Please, if You think, so Oracle way is good, correct it. Best regards Pavel
Done Regards Pavel Stehule
TODO updated. --------------------------------------------------------------------------- Pavel Stehule wrote: > Done > > Regards > Pavel Stehule > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073