STRICT function returning a composite type

From: "Alexander M(dot) Pravking" <fduch(at)antar(dot)bryansk(dot)ru>
To: pgsql-sql(at)postgresql(dot)org
Subject: STRICT function returning a composite type
Date: 2003-11-13 16:08:00
Message-ID: 20031113160800.GL69653@dyatel.antar.bryansk.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2003-11-13 17:14:27 Re: STRICT function returning a composite type
Previous Message Louise Cofield 2003-11-13 15:22:33 Re: Looks are important