<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>