Re: [HACKERS] Re: [SQL] 2 questions.

> > 1) functions taking or returning "pass by value" arguments
> > cannot signal that the returned value should
> > actually be NULL. So they must throw an error instead. That's why
> > char_length() doesn't behave gracefully with nulls.

1a) functions taking only "pass by value" arguments are never called
with NULL arguments, since Postgres cannot indicate to the routine that
there is a NULL value (it uses a null pointer to indicate this with
"pass by reference" arguments, such as float8 or text).

> However SQL92 has some functions to help with nulls and I think we
> NEED to have these on PostgreSQL.
> For example, I don't know how to compute fields containing nulls,
> because the result is always null.
>
> AFAIK SQL92 has at least two functions that one can use to decide what
> to do with nulls. (i.e.:)
> o CASE WHEN ... ELSE ... END
> and
> o COALESCE()
> other databases have similar functions, for example Oracle has the
> function NVL() to translate nulls to an arbitrary value.

So Oracle does not have the CASE construct? That seems to be a rich area
for work in v6.5...

> I tried to write a function to emulate NVL() but it doesn't work
> because nulls have a strange behavior.
>
> int nvl(int arg)
> {
> if(arg) return arg;
> return 0;
> }
>
> Any ideas?

You are running into the "pass by value" item (1a) above. And anyway,
you would not be able to do what you want since there is currently no
way to signal back that the input is NULL rather than zero.

In the long run, we would need to either convert all user-accessible
types to be "pass by reference", or would need to implement another
mechanism for signalling NULLness as a return condition (perhaps with a
global variable since each Postgres backend is single-threaded).

This would be a good topic to revisit after v6.4 is released...

- Tom