Question about STRICT

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