Date: Thu, 13 Nov 2003 19:08:00 +0300
From: "Alexander M. Pravking" <fduch@antar.bryansk.ru>
To: pgsql-sql@postgresql.org
Subject: STRICT function returning a composite type
Message-ID: <20031113160800.GL69653@dyatel.antar.bryansk.ru>
Mime-Version: 1.0
Content-Type: text/plain; charset=koi8-r
Content-Disposition: inline
User-Agent: Mutt/1.4.1i

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 < edate2 THEN 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, compiled by GCC 2.95.4


-- 
Fduch M. Pravking