Re: NULLs and composite types

On 2013-12-12 11:25, David Johnston wrote:
> Dean Gibson (DB Administrator)-2 wrote
>> What's going on? I can provide more detail if requested. Of course, an
>> obvious workaround is to use in a VIEW:
>>
>> ... NULLIF( location, ROW( NULL, NULL )::"GeoPosition" ) ...
>>
>> but I'd like to know the cause.
> Cannot test right now but the core issue is that IS NULL on a record type
> evaluates both the scalar whole and the sub-components. Try using IS [NOT]
> DISTINCT FROM with various target expressions and see if you can get
> something more sane.
>
> David J.

Yes, "SELECT ROW( NULL, NULL ) IS NULL;" produces TRUE, and "SELECT ROW(
NULL, NULL ) IS NOT DISTINCT FROM NULL;" produces FALSE.

However, my problem is not that the comparison tests produce different
results; that's just a symptom. My problem is that PostgreSQL is
*changing* a NULL record value, to a record with NULLs for the component
values, when I attempt to INSERT or UPDATE it into a different field.
That means in php (for example), that retrieving what started out as a
NULL record (and in php retrieves an empty string), becomes a record
with NULL values (and in php retrieves a "(,)" string). Yes, I can test
for that in php, but problems/work-arounds need to be solved in the
component that causes them.

However, I have found a satisfactory work-around in the TRIGGER function
to the problem: In my INSERT and UPDATE statements, I use:

... NULLIF( record_row.location, ROW( NULL, NULL )::"GeoPosition" ) ...

when adding or changing a value.

Note that setting "record_row.location" to NULL in PL/pgSQL just before
the INSERT or UPDATE *does not solve the problem*, and tests of the
value before and after setting the value in a record field (retrieved
via a CURSOR FOR SELECT ...) shows that the value does not change to
fully NULL.

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.