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