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.
