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

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

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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)kix(dot)fsv(dot)cvut(dot)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(at)kix(dot)fsv(dot)cvut(dot)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

[ moving to -hackers for a wider audience ]

Today's issue: should the GREATEST/LEAST functions be strict (return
null if any input is null) or not (return null only if all inputs are
null, else return the largest/smallest of the non-null inputs)?

Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
> On Thu, 23 Jun 2005, Tom Lane wrote:
>> Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)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.

> 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.

I'm still favoring non-strict but it deserves more than two votes.
Anybody else have an opinion?

regards, tom lane

Tom Lane wrote:

>[ moving to -hackers for a wider audience ]
>
>Today's issue: should the GREATEST/LEAST functions be strict (return
>null if any input is null) or not (return null only if all inputs are
>null, else return the largest/smallest of the non-null inputs)?
>
>
>

My initial reaction was to say "not strict", and since that's apparently
what Oracle does that reinforces it for me.

cheers

andrew

On 6/24/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> [ moving to -hackers for a wider audience ]
>
> Today's issue: should the GREATEST/LEAST functions be strict (return
> null if any input is null) or not (return null only if all inputs are
> null, else return the largest/smallest of the non-null inputs)?
>

[snip]

>
> > Please, if You think, so Oracle way is good, correct it.
>
> I'm still favoring non-strict but it deserves more than two votes.
> Anybody else have an opinion?
>
> regards, tom lane
>

My $0.02: I'd prefer the non-strict version.

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org

On Fri, 2005-06-24 at 09:21, Tom Lane wrote:
> [ moving to -hackers for a wider audience ]
>
> Today's issue: should the GREATEST/LEAST functions be strict (return
> null if any input is null) or not (return null only if all inputs are
> null, else return the largest/smallest of the non-null inputs)?
>
> Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
> > On Thu, 23 Jun 2005, Tom Lane wrote:
> >> Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)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.
>
> > 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.
>
> I'm still favoring non-strict but it deserves more than two votes.
> Anybody else have an opinion?
>

If the sql spec has nothing to say on it, then we should probably
support Oracles take, since this seems like an Oracleism anyway.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Fri, Jun 24, 2005 at 09:21:25AM -0400, Tom Lane wrote:
> [ moving to -hackers for a wider audience ]
>
> Today's issue: should the GREATEST/LEAST functions be strict (return
> null if any input is null) or not (return null only if all inputs
> are null, else return the largest/smallest of the non-null inputs)?

I'd say non-strict unless SQL:2003 says different.

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

Done

Regards
Pavel Stehule

TODO updated.

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

Pavel Stehule wrote:
> Done
>
> Regards
> Pavel Stehule
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073