From: Alexander M. Pravking <fduch@antar.bryansk.ru>
To: pgsql-sql@postgresql.org
Subject: STRICT function returning a composite type
Date: Thu, 13 Nov 2003 19:08:00 +0300
Message-ID: <20031113160800.GL69653@dyatel.antar.bryansk.ru> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fduch@antar.bryansk.ru
Subject: Re: STRICT function returning a composite type
In-Reply-To: <20031113160800.GL69653@dyatel.antar.bryansk.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox