Re: NonNullValue() error in 8.4

On Tue, Oct 12, 2010 at 11:28 AM, Rebecca Cooper
<rebecca(dot)cooper(at)newforestnpa(dot)gov(dot)uk> wrote:
> We have recently upgraded to 8.4 from 8.3 and are experiencing problems with
> a client application.  The log files record multiple entries of -
>
> SELECT NonNullValue(attname) AS has_oid FROM pg_attribute
>
> ERROR:  function nonnullvalue(name) does not exist at character 664
>
> Was this function written out of 8.4?  I can’t find much about this function
> in the documentation, but I will keep looking in advance of a response.

It looks like these functions were intentionally never documented:
http://archives.postgresql.org/pgsql-docs/2004-08/msg00015.php

At any rate, the nonnullvalue() function your code is trying to use
was ripped out in this commit in 2008:
http://archives.postgresql.org/pgsql-committers/2008-10/msg00034.php

which is why it's not in 8.4. I think a mention of this change should
be made in the 8.4 release notes, it's the first time I'm seeing this
change documented anywhere.

Anyway, if you are able to modify your client application, I think you
should be able to change queries using nonnullvalue() like this:
SELECT NonNullValue(attname) AS has_oid FROM pg_attribute;

to use a CASE statement like so:

SELECT (CASE WHEN attname IS NULL THEN true ELSE false END) AS
has_oid FROM pg_attribute;

which should work fine on 8.4.

Josh