IFNULL -> COALESCE

Guys, I've recently being going back over code from Ingres and porting
it over to PostgreSQL. Heavy use was made of the IFNULL function, this
function simply returns the 2nd argument if the first is
NULL. Consider the following query:

SELECT COALESCE(MAX(id), 0) + 1 from test;

can be replaced by the following PostgreSQL query:

SELECT COALESCE(MAX(id), 0) + 1 from test;

I've manually done this, but wouldn't this be a useful auto-tranlation
to make in the parser? Aid to porting and all...

Yeah, I know i should be using a SERIAL column, that's later work...

Regards, Lee.

> Guys, I've recently being going back over code from Ingres and porting
> it over to PostgreSQL. Heavy use was made of the IFNULL function, this
> function simply returns the 2nd argument if the first is
> NULL. Consider the following query:
>
> SELECT COALESCE(MAX(id), 0) + 1 from test;
>
> can be replaced by the following PostgreSQL query:
>
> SELECT COALESCE(MAX(id), 0) + 1 from test;

Ummm...did you make a mistake here? Those statements are identical...

Chris

Christopher Kings-Lynne writes:
> lkindness(at)csl(dot)co(dot)uk writes:
> > SELECT COALESCE(MAX(id), 0) + 1 from test;
> > can be replaced by the following PostgreSQL query:
> > SELECT COALESCE(MAX(id), 0) + 1 from test;
> Ummm...did you make a mistake here? Those statements are
> identical...

Okay, lets try that again...

SELECT IFNULL(MAX(id), 0) + 1 from test;

can be replaced by the following PostgreSQL query:

SELECT COALESCE(MAX(id), 0) + 1 from test;

Thanks, Lee.

Lee Kindness <lkindness(at)csl(dot)co(dot)uk> writes:
> Okay, lets try that again...
> SELECT IFNULL(MAX(id), 0) + 1 from test;
> can be replaced by the following PostgreSQL query:
> SELECT COALESCE(MAX(id), 0) + 1 from test;

For any specific datatype that you might need this for, you could
provide a user-defined IFNULL function to avoid having to translate
your code. Might get a bit tedious if you are doing it for a lot
of different datatypes, however.

Not sure if it's worth adding a keyword and a grammar production
to get Postgres to do this for you. If it were part of a full-court
press to improve our Oracle compatibility, I wouldn't object, but
I'm not sure I see the point of doing just the one nonstandard
feature.

regards, tom lane

On Fri, 8 Feb 2002, Lee Kindness wrote:

> Christopher Kings-Lynne writes:
> > lkindness(at)csl(dot)co(dot)uk writes:
> > > SELECT COALESCE(MAX(id), 0) + 1 from test;
> > > can be replaced by the following PostgreSQL query:
> > > SELECT COALESCE(MAX(id), 0) + 1 from test;
> > Ummm...did you make a mistake here? Those statements are
> > identical...
>
> Okay, lets try that again...
>
> SELECT IFNULL(MAX(id), 0) + 1 from test;
>
> can be replaced by the following PostgreSQL query:
>
> SELECT COALESCE(MAX(id), 0) + 1 from test;

Might be nice to have it done automatically, but as a workaround
why not just define ifnull(int, int) - or whatever types are
necessary.

create function ifnull(int, int) returns int as
'select coalesce($1, $2);' language 'sql';
should work for 7.1 and above unless I'm missing something.

Oh, i'd agree - it's not really worth the hassle adding the code to
automatically do this. Useful to have it mentioned in the archives so
someone else coming up against the same issue can pick up on it
quicker...

Got me thinking about an option for ecpg to report about any
non-standard/user-defined functions used in the source (which of
course it assumes are such and just lets them through). Also that
'sqlca is included by default' message added for 7.2 is annoying!

And Bruce, yeah there's a lock ;)

Regards, Lee Kindness.

Tom Lane writes:
> Lee Kindness <lkindness(at)csl(dot)co(dot)uk> writes:
> > Okay, lets try that again...
> > SELECT IFNULL(MAX(id), 0) + 1 from test;
> > can be replaced by the following PostgreSQL query:
> > SELECT COALESCE(MAX(id), 0) + 1 from test;
>
> For any specific datatype that you might need this for, you could
> provide a user-defined IFNULL function to avoid having to translate
> your code. Might get a bit tedious if you are doing it for a lot
> of different datatypes, however.
>
> Not sure if it's worth adding a keyword and a grammar production
> to get Postgres to do this for you. If it were part of a full-court
> press to improve our Oracle compatibility, I wouldn't object, but
> I'm not sure I see the point of doing just the one nonstandard
> feature.
>
> regards, tom lane

Lee Kindness writes:

> Got me thinking about an option for ecpg to report about any
> non-standard/user-defined functions used in the source (which of
> course it assumes are such and just lets them through). Also that
> 'sqlca is included by default' message added for 7.2 is annoying!

No kidding. Can we remove that for 7.2.1?

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> 'sqlca is included by default' message added for 7.2 is annoying!

> No kidding. Can we remove that for 7.2.1?

I didn't understand why it was put in in the first place. There's
no need for it.

regards, tom lane

On Mon, Feb 11, 2002 at 09:44:27AM +0000, Lee Kindness wrote:
> Oh, i'd agree - it's not really worth the hassle adding the code to
> automatically do this. Useful to have it mentioned in the archives so
> someone else coming up against the same issue can pick up on it
> quicker...
>
> Got me thinking about an option for ecpg to report about any
> non-standard/user-defined functions used in the source (which of
> course it assumes are such and just lets them through). Also that
> 'sqlca is included by default' message added for 7.2 is annoying!

That's actually something needed for FIPS (US federal gov't standard)
although it's optional (not mentionec at all?) for ANSI or ISO: a
'flagger' that reports all non-standard extensions.

Ross

Tom Lane writes:

> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> >> 'sqlca is included by default' message added for 7.2 is annoying!
>
> > No kidding. Can we remove that for 7.2.1?
>
> I didn't understand why it was put in in the first place. There's
> no need for it.

As it stands, sqlca will actually be included twice, so the warning has
some merit. But it might be better to actually prevent the second
inclusion.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> As it stands, sqlca will actually be included twice, so the warning has
> some merit. But it might be better to actually prevent the second
> inclusion.

Why? sqlca.h has an #ifndef guard, so there's no harm done. I'd vote
for just suppressing the check and notice completely.

regards, tom lane

Peter Eisentraut writes:
> Tom Lane writes:
> > I didn't understand why it was put in in the first place. There's
> > no need for it.
> As it stands, sqlca will actually be included twice, so the warning has
> some merit. But it might be better to actually prevent the second
> inclusion.

As I understand it with 7.1 you HAD to have an 'EXEC SQL INCLUDE
sqlca' line for things to work (assuming you actually access the sqlca
structure). With 7.2 this file is now automatically included (whether
you need it or not) and when you explicitly tell the precompiler what
you're using you get a warning! Imagine the response if a C compiler
was compiling the following program:

#include <stdio.h>

int main(int argc, char **argv)
{
printf("Hello world!\n");
}

and gave you a warning for including stdio.h! For reference gcc must
be doing something similar to ecpg - because you don't NEED to include
stdio.h (which is bad).

It's nothing major... just annoying!

Best Regards, Lee.