Stored function not accepting null value?
От | Deirdre Hall |
---|---|
Тема | Stored function not accepting null value? |
Дата | |
Msg-id | 03f401c9b2f6$a3c2ee90$5062140a@dc06012008 обсуждение исходный текст |
Ответы |
Re: Stored function not accepting null value?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-sql |
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I am using a stored function to determine whether a table needs an update or insert, based on the merge_dbfunction in the postgres documentation. (Not currently including it below because it’s rather long, but other thanthe fact that it contains 27 values, it’s pretty much the same as that example.)</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">It works fine as long as all the values are defined: </span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">select event_archive (7, 2, 2, 2, 3, text 'TIMS', 3, text 'I-90', 2, 3, 1.2, 1.3, 3, 1, 2, text 'important',timestamp '2008-01-01', </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">timestamp '2008-01-01', timestamp '2008-01-01', timestamp '2008-01-01', timestamp '2008-01-01', text'2 hours', timestamp '2008-01-01', timestamp '2008-01-01', text 'oak', text 'main', 2);</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">but if I change any of the above values to null, the function simply doesn’t operate. </span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">select event_archive (7, 2, 2, 2, 3, text 'TIMS', 3, text 'I-90', 2, 3, 1.2, 1.3, 3, 1, 2, text 'important',timestamp '2008-01-01', </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">timestamp '2008-01-01', timestamp '2008-01-01', timestamp '2008-01-01', timestamp '2008-01-01', text'2 hours', timestamp '2008-01-01', timestamp '2008-01-01', text 'oak', text 'main', null);</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">It returns:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Total query runtime: 0 ms.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">1 rows retrieved.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">versus a runtime of about 13ms when it successfully updates or inserts. I put raise notices into the functionto try to debug, and it never even hits the first one in the first LOOP when there is a null value.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">The only column in the table that is declared not null would be the 7 in the above statement (id), allother fields should be able to accept a null value. Is this a known limitation with functions, or am I doing somethingincorrectly?</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Thanks,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">DHall</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font></div><br /><hr size="1" /><font face="arial" size="2"> This communication may containinformation that is confidential, privileged or subject to copyright. If you are not the intended recipient, pleaseadvise by return e-mail and delete the message and any attachments immediately without reading, copying or forwardingto others.</font><br />
В списке pgsql-sql по дате отправления: