Thread: 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