Re: NULLs and composite types

<br /><div class="moz-cite-prefix">On 2013-12-12 11:25, David Johnston wrote:<br /></div><blockquote
cite="mid:1386876331115-5783187.post@n5.nabble.com"type="cite"><pre wrap="">Dean Gibson (DB Administrator)-2 wrote
 
</pre><blockquote type="cite"><pre wrap="">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.
</pre></blockquote><pre wrap="">
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.
</pre></blockquote><br /> Yes, "SELECT ROW( NULL, NULL ) IS NULL;" produces TRUE, and "SELECT ROW( NULL, NULL ) IS NOT
DISTINCTFROM NULL;" produces FALSE.<br /><br /> However, my problem is not that the comparison tests produce different
results; that's just a symptom.  My problem is that PostgreSQL is <b>changing</b> a NULL record value, to a record with
NULLsfor 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
withNULL values (and in php retrieves a "(,)" string).  Yes, I can test for that in php, but problems/work-arounds need
tobe solved in the component that causes them.<br /><br /> However, I have found a satisfactory work-around in the
TRIGGERfunction to the problem:  In my INSERT and UPDATE statements, I use:<br /><br /> ... NULLIF(
record_row.location,ROW( NULL, NULL )::"GeoPosition" ) ...<br /><br /> when adding or changing a value.<br /><br />
Notethat setting "record_row.location" to NULL in PL/pgSQL just before the INSERT or UPDATE <b>does not solve the
problem</b>,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.<br /><br /><br /><pre class="moz-signature" cols="72">--

Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>