Thread: Question about STRICT

<p class="MsoNormal">Could someone please clarify the difference between <tt><span lang="EN" style="font-size: 10pt;
line-height:115%; color: black;">"RETURNS NULL ON NULL INPUT</span></tt><span lang="EN" style="font-size: 9pt;
line-height:115%; font-family: "Verdana","sans-serif"; color: black;">" or "</span><tt><span lang="EN"
style="font-size:10pt; line-height: 115%; color: black;">STRICT" when creating a function. Do both options exist
becauseof historical reasons/SQL standard compliance?<br /></span></tt><p class="MsoNormal"><tt><span lang="EN"
style="font-size:10pt; line-height: 115%; color: black;"><br /> Shouldn't we raise an error when calling a function
withNULL arguments values if the function is created as STRICT?<br /> (This would of course have an impact on checking
forNULLs on arguments defaults if the above is implemented.)</span></tt><br /><p class="MsoNormal">-- <br /><pre
class="moz-signature"cols="72">Regards,
 
Gevik</pre>

On Wed, Jun 3, 2009 at 9:45 AM, Gevik Babakhani <pgdev@xs4all.nl> wrote:
> Could someone please clarify the difference between "RETURNS NULL ON NULL
> INPUT" or "STRICT" when creating a function.

They're synonyms.

> Do both options exist because
> of historical reasons/SQL standard compliance?

One or the other, not sure which.

> Shouldn't we raise an error when calling a function with NULL arguments
> values if the function is created as STRICT?

No, what they do is return NULL automatically. The function doesn't
have to check for NULL arguments itself.


-- 
greg

>> Shouldn't we raise an error when calling a function with NULL arguments
>> values if the function is created as STRICT?
> 
> No, what they do is return NULL automatically. The function doesn't
> have to check for NULL arguments itself.

The "RETURNS NULL ON NULL INPUT" is logical and does the above 
accordingly. But when a function is STRICT you kind of expect to have an 
notification, perhaps an error if a value for an argument is NULL.

STRICT is sort of puzzling when you want to make sure a function is only 
called if none of the arguments are NULL.

With STRICT, the function is "called" anyway and returns NULL, witch 
results the application code to happily execute further without noticing 
that calling the function did not do anything.

I am thinking about the following situation:

create table table1
(    col1 int,    col2 varchar
);

create or replace function insert_test(int,varchar) returns void as
$$    insert into table1 (col1,col2) values ($1,$2);
$$
language sql strict;

select * from insert_test(null,'a');

select * from table1;

-- 
Regards,
Gevik

On Wed, Jun 3, 2009 at 11:04 AM, Gevik Babakhani <pgdev@xs4all.nl> wrote:
> The "RETURNS NULL ON NULL INPUT" is logical and does the above accordingly.
> But when a function is STRICT you kind of expect to have an notification,
> perhaps an error if a value for an argument is NULL.

Uhm, you might but I'm not sure why. That's not what STRICT does. It's
a synonym for RETURNS NULL ON NULL INPUT.

-- 
greg

Greg Stark wrote:
> On Wed, Jun 3, 2009 at 11:04 AM, Gevik Babakhani <pgdev@xs4all.nl> wrote:
>> The "RETURNS NULL ON NULL INPUT" is logical and does the above accordingly.
>> But when a function is STRICT you kind of expect to have an notification,
>> perhaps an error if a value for an argument is NULL.
> 
> Uhm, you might but I'm not sure why. That's not what STRICT does. It's
> a synonym for RETURNS NULL ON NULL INPUT.
> 

Perhaps it is an idea to have something like: "RAISE ERROR ON NULL INPUT"

-- 
Regards,
Gevik

Gevik Babakhani <pgdev@xs4all.nl> writes:
> Perhaps it is an idea to have something like:
>   "RAISE ERROR ON NULL INPUT"

[ shrug... ]  There's really been no demand for that.  If you want a
particular function to do it, you can put suitable tests and error
reports into that function.  I can't see us adding extra cycles into
the core function-calling code (which is a very hot hot-spot) for a
feature with so little demand.
        regards, tom lane

Tom Lane wrote:
> Gevik Babakhani <pgdev@xs4all.nl> writes:
>> Perhaps it is an idea to have something like:
>>   "RAISE ERROR ON NULL INPUT"
> 
> [ shrug... ]  There's really been no demand for that.  If you want a
> particular function to do it, you can put suitable tests and error
> reports into that function.  I can't see us adding extra cycles into
> the core function-calling code (which is a very hot hot-spot) for a
> feature with so little demand.

Understood. Thank you (Tom and Greg) for clarifying this.

-- 
Regards,
Gevik