Обсуждение: STRICT function returning a composite type

I noted that such a function returns an empty rowset if a NULL value is
passed as an argument. Is it a bug or feature? I wish it was a feature,
because I probably want to use this behavour.

Here's an example:

CREATE TYPE ts_bounds AS (       sdate   timestamptz,       edate   timestamptz
);

CREATE OR REPLACE FUNCTION overlap_bounds(timestamptz, timestamptz, timestamptz, timestamptz)
RETURNS ts_bounds AS '
DECLARE       sdate1  ALIAS FOR $1;       edate1  ALIAS FOR $2;       sdate2  ALIAS FOR $3;       edate2  ALIAS FOR $4;
     res     ts_bounds%rowtype;
 
BEGIN       res.sdate := CASE WHEN sdate1 > sdate2 THEN sdate1 ELSE sdate2 END;       res.edate := CASE WHEN edate1 <
edate2THEN edate1 ELSE edate2 END;       IF res.sdate > res.edate THEN               res.sdate := NULL;
res.edate:= NULL;       END IF;       RETURN res;
 
END' LANGUAGE 'plPgSQL' STRICT;


fduch=# SELECT * from overlap_bounds('-infinity', 'today', 'yesterday', 'infinity');        sdate          |
edate
------------------------+------------------------2003-11-12 00:00:00+03 | 2003-11-13 00:00:00+03
(1 row)

fduch=# SELECT * from overlap_bounds('-infinity', 'today', 'yesterday', null);sdate | edate
-------+-------
(0 rows)


What I want is to get no rows if given intervals don't overlap instead of:
fduch=# SELECT * from overlap_bounds('-infinity', 'yesterday', 'today', 'infinity');sdate | edate
-------+-------      |
(1 row)

Is it possible without returning SETOF ts_bounds?


fduch=# SELECT version();                              version
---------------------------------------------------------------------PostgreSQL 7.3.4 on i386-portbld-freebsd4.8,
compiledby GCC 2.95.4
 


-- 
Fduch M. Pravking

On Thursday 13 November 2003 16:08, Alexander M. Pravking wrote:
> I noted that such a function returns an empty rowset if a NULL value is
> passed as an argument. Is it a bug or feature? I wish it was a feature,
> because I probably want to use this behavour.

From the SQL commands section of the manual:

RETURNS NULL ON NULL INPUT or STRICT indicates that the function always 
returns NULL whenever any of its arguments are NULL. If this parameter is 
specified, the function is not executed when there are NULL arguments; 
instead a NULL result is assumed automatically.

--  Richard Huxton Archonet Ltd

On Thu, Nov 13, 2003 at 05:14:27PM +0000, Richard Huxton wrote:
> RETURNS NULL ON NULL INPUT or STRICT indicates that the function always 
> returns NULL whenever any of its arguments are NULL. If this parameter is 
> specified, the function is not executed when there are NULL arguments; 
> instead a NULL result is assumed automatically.

Does "NULL result" mean an empty rowset if the function returns a record?

-- 
Fduch M. Pravking

"Alexander M. Pravking" <fduch@antar.bryansk.ru> writes:
> On Thu, Nov 13, 2003 at 05:14:27PM +0000, Richard Huxton wrote:
>> RETURNS NULL ON NULL INPUT or STRICT indicates that the function always 
>> returns NULL whenever any of its arguments are NULL.

> Does "NULL result" mean an empty rowset if the function returns a record?

No, it means a null record.  "Empty rowset" would apply to a function
declared to return SETOF something.   (I believe that is how we
interpret the concept of strictness for functions returning sets.)
        regards, tom lane

On Thu, Nov 13, 2003 at 12:27:58PM -0500, Tom Lane wrote:
> "Alexander M. Pravking" <fduch@antar.bryansk.ru> writes:
> > Does "NULL result" mean an empty rowset if the function returns a record?
> 
> No, it means a null record.  "Empty rowset" would apply to a function
> declared to return SETOF something.   (I believe that is how we
> interpret the concept of strictness for functions returning sets.)

Very well then... Can I return a null record from such function
explicitly? Sorry, I could't find it anywhere in docs or examples.


-- 
Fduch M. Pravking

"Alexander M. Pravking" <fduch@antar.bryansk.ru> writes:
> Very well then... Can I return a null record from such function
> explicitly? Sorry, I could't find it anywhere in docs or examples.

Not sure.  Seems like you should be able to, but I've never tried it.
        regards, tom lane

On Thu, Nov 13, 2003 at 12:35:41PM -0500, Tom Lane wrote:
> "Alexander M. Pravking" <fduch@antar.bryansk.ru> writes:
> > Very well then... Can I return a null record from such function
> > explicitly? Sorry, I could't find it anywhere in docs or examples.
> 
> Not sure.  Seems like you should be able to, but I've never tried it.

Thanks for a quick response, guys. I'll try to find it myself and
will let you know if I did ;)

-- 
Fduch M. Pravking