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

Re: Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic

From
Bruce Momjian
Date:
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

Re: Function's LEAST, GREATEST and DECODE (Oracle vararg

From
Pavel Stehule
Date:
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
>
>


Re: Function's LEAST, GREATEST and DECODE (Oracle vararg

From
Bruce Momjian
Date:
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

Re: Function's LEAST, GREATEST and DECODE (Oracle vararg

From
Bruce Momjian
Date:
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

Re: Function's LEAST, GREATEST and DECODE (Oracle vararg

From
Tom Lane
Date:
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

Re: Function's LEAST, GREATEST and DECODE (Oracle vararg

From
Bruce Momjian
Date:
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

Re: Function's LEAST, GREATEST and DECODE (Oracle vararg

From
Tom Lane
Date:
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

Re: Function's LEAST, GREATEST and DECODE (Oracle vararg

From
Bruce Momjian
Date:
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

Re: Function's LEAST, GREATEST and DECODE (Oracle vararg

From
Pavel Stehule
Date:
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

Re: Function's LEAST, GREATEST and DECODE (Oracle vararg

From
Pavel Stehule
Date:
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


ToDo: Allow PL/pgSQL EXECUTE query_var INTO record_var;

From
Pavel Stehule
Date:
Done

Regards
Pavel Stehule



Re: ToDo: Allow PL/pgSQL EXECUTE query_var INTO record_var;

From
Bruce Momjian
Date:
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