pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStored function not accepting null value?
3+ messages / 2 participants
[nested] [flat]
* Stored function not accepting null value?
@ 2009-04-01 18:17 Deirdre Hall <d.hall@delcan.com>
2009-04-01 19:01 ` Re: Stored function not accepting null value? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Deirdre Hall @ 2009-04-01 18:17 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Stored function not accepting null value?
2009-04-01 18:17 Stored function not accepting null value? Deirdre Hall <d.hall@delcan.com>
@ 2009-04-01 19:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-01 19:20 ` Re: Stored function not accepting null value? Deirdre Hall <d.hall@delcan.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2009-04-01 19:01 UTC (permalink / raw)
To: Deirdre Hall <d.hall@delcan.com>; +Cc: pgsql-sql
"Deirdre Hall" <d.hall@delcan.com> writes:
> 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.)
Well, not including it is a good way to ensure that no one will really
know what the problem is ... but I wonder whether you declared the
function STRICT. That'd prevent it from being called with a NULL
argument, which seems to fit the reported behavior.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Stored function not accepting null value?
2009-04-01 18:17 Stored function not accepting null value? Deirdre Hall <d.hall@delcan.com>
2009-04-01 19:01 ` Re: Stored function not accepting null value? Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-04-01 19:20 ` Deirdre Hall <d.hall@delcan.com>
0 siblings, 0 replies; 3+ messages in thread
From: Deirdre Hall @ 2009-04-01 19:20 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
And that would be where I went wrong. Thanks.
DHall
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, April 01, 2009 2:01 PM
To: Deirdre Hall
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Stored function not accepting null value?
"Deirdre Hall" <d.hall@delcan.com> writes:
> 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.)
Well, not including it is a good way to ensure that no one will really
know what the problem is ... but I wonder whether you declared the
function STRICT. That'd prevent it from being called with a NULL
argument, which seems to fit the reported behavior.
regards, tom lane
____
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-04-01 19:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-01 18:17 Stored function not accepting null value? Deirdre Hall <d.hall@delcan.com>
2009-04-01 19:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-01 19:20 ` Deirdre Hall <d.hall@delcan.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox