Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3A9A9633FAD for ; Wed, 1 Apr 2009 15:27:23 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 87510-09 for ; Wed, 1 Apr 2009 15:27:15 -0300 (ADT) X-Greylist: delayed 00:09:29.961444 by SQLgrey-1.7.6 Received: from sch-smb2.sch.delcan.com (schaumburg.delcan.net [198.235.16.19]) by mail.postgresql.org (Postfix) with ESMTP id B066E634B38 for ; Wed, 1 Apr 2009 15:27:17 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by sch-smb2.sch.delcan.com (Postfix) with ESMTP id 6B880BA6C02A for ; Wed, 1 Apr 2009 13:17:30 -0500 (CDT) Received: from sch-smb2.sch.delcan.com ([127.0.0.1]) by localhost (sch-smb2.sch.delcan.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id V2rL6-nL3mdc for ; Wed, 1 Apr 2009 13:17:30 -0500 (CDT) Received: by sch-smb2.sch.delcan.com (Postfix, from userid 101) id 58916BA6C02C; Wed, 1 Apr 2009 13:17:30 -0500 (CDT) Received: from sch-smb2.sch.delcan.com (SCH-SMB2.sch.delcan.com [10.20.98.23]) by sch-smb2.sch.delcan.com (Postfix) with ESMTP id 51E0DBA6C029 for ; Wed, 1 Apr 2009 13:17:30 -0500 (CDT) From: "Deirdre Hall" To: Subject: Stored function not accepting null value? Date: Wed, 1 Apr 2009 13:17:30 -0500 (CDT) Message-ID: <03f401c9b2f6$a3c2ee90$5062140a@dc06012008> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_03F5_01C9B2CC.BAECE690" X-Mailer: Microsoft Office Outlook 11 X-Mailer: Zimbra 5.0.11_GA_2696.RHEL4 (ZimbraConnectorForOutlook/5.0.2817.11) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350 thread-index: Acmy9qL6ZstUTmv4QP6kDF3V2ukBtw== X-Originating-IP: [65.196.233.130] X-Copyrighted-Material: Please visit http://www.delcan.com/privacy.htm X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.82 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, MIME_QP_LONG_LINE=1.819 X-Spam-Level: * X-Archive-Number: 200904/4 X-Sequence-Number: 32350 This is a multi-part message in MIME format. ------=_NextPart_000_03F5_01C9B2CC.BAECE690 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I am using a stored function to determine whether a table needs an update or insert, based on the merge_db function in the postgres documentation. (Not currently including it below because it's rather long, but other than the fact that it contains 27 values, it's pretty much the same as that example.) It works fine as long as all the values are defined: 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', 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); but if I change any of the above values to null, the function simply doesn't operate. 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', 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); It returns: Total query runtime: 0 ms. 1 rows retrieved. versus a runtime of about 13ms when it successfully updates or inserts. I put raise notices into the function to try to debug, and it never even hits the first one in the first LOOP when there is a null value. The only column in the table that is declared not null would be the 7 in the above statement (id), all other fields should be able to accept a null value. Is this a known limitation with functions, or am I doing something incorrectly? Thanks, DHall ____ This communication may contain information that is confidential, privileged or subject to copyright. If you are not the intended recipient, please advise by return e-mail and delete the message and any attachments immediately without reading, copying or forwarding to others. ------=_NextPart_000_03F5_01C9B2CC.BAECE690 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I am using a stored function to determine whether a = table needs an update or insert, based on the merge_db function in the = postgres documentation. (Not currently including it below because it’s = rather long, but other than the fact that it contains 27 values, it’s = pretty much the same as that example.)

 

It works fine as long as all the values are defined: =

 

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

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

 

but if I change any of the above values to null, the function simply doesn’t operate.

 

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

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

 

It returns:

Total query runtime: 0 = ms.

1 rows retrieved.

 

versus a runtime of about 13ms when it successfully = updates or inserts. I put raise notices into the function to try to debug, and = it never even hits the first one in the first LOOP when there is a null value.

 

The only column in the table that is declared not = null would be the 7 in the above statement (id), all other fields should be able to = accept a null value.  Is this a known limitation with functions, or am I = doing something incorrectly?

 

Thanks,

DHall

 


=0AThis communication may contain information that is confidential, privileged or subject to copyright. If you are not the intended recipient, please advise by return e-mail and delete the message and any attachments immediately without reading, copying or forwarding to others.
------=_NextPart_000_03F5_01C9B2CC.BAECE690--