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.

Fri, 8 Feb 2002 16:37:03 +0000
Lee Kindness <lkindness@csl.co.uk>
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;

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

Chris


Sat, 9 Feb 2002 01:12:10 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: IFNULL -> COALESCE
Christopher Kings-Lynne writes:
 > lkindness@csl.co.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.

Fri, 8 Feb 2002 17:17:03 +0000
Lee Kindness <lkindness@csl.co.uk>
Re: IFNULL -> COALESCE
Lee Kindness <lkindness@csl.co.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

Fri, 08 Feb 2002 13:23:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: IFNULL -> COALESCE 
On Fri, 8 Feb 2002, Lee Kindness wrote:

> Christopher Kings-Lynne writes:
>  > lkindness@csl.co.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.




Fri, 8 Feb 2002 10:29:00 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: IFNULL -> COALESCE
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@csl.co.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

Mon, 11 Feb 2002 09:44:27 +0000
Lee Kindness <lkindness@csl.co.uk>
Re: IFNULL -> COALESCE 
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@gmx.net


Mon, 11 Feb 2002 11:39:21 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: IFNULL -> COALESCE 
Peter Eisentraut <peter_e@gmx.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

Mon, 11 Feb 2002 11:46:09 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: IFNULL -> COALESCE 
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


Mon, 11 Feb 2002 15:06:20 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: IFNULL -> COALESCE
Tom Lane writes:

> Peter Eisentraut <peter_e@gmx.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@gmx.net


Mon, 11 Feb 2002 16:56:00 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
sqlca warning (was Re: IFNULL -> COALESCE)
Peter Eisentraut <peter_e@gmx.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

Mon, 11 Feb 2002 16:58:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: sqlca warning (was Re: IFNULL -> COALESCE) 
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.

Tue, 12 Feb 2002 09:34:03 +0000
Lee Kindness <lkindness@csl.co.uk>
sqlca warning (was Re: IFNULL -> COALESCE)