From: | Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
---|---|
To: | Daniel Lenski <dlenski(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: sign function with INTERVAL? |
Date: | 2016-04-13 19:08:58 |
Message-ID: | 570E994A.4030002@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On 4/13/16 1:36 PM, Daniel Lenski wrote:
> Hi all,
> Is there a good reason why the SIGN() function does not work with the
> INTERVAL type? (It is only defined for numeric types.)
> (http://www.postgresql.org/docs/9.5/static/functions-math.html)
The only thing that comes to mind is you can get some strange
circumstances with intervals, like '-1 mons +4 days'. I don't think that
precludes sign() though.
> What I have come up with is this rather inelegant and error-prone case
> statement:
How is it error prone?
> case when x is null then null x>interval '0' then +1 when x<interval
> '0' then -1 when x=interval '0' then 0 end
You don't need to handle null explicitly. You could do
SELECT CASE WHEN x > interval '0' THEN 1 WHEN x < interval '0' THEN -1
WHEN x = interval '0' THEN 0 END
Or, you could do...
CREATE FUNCTION sign(interval) RETURNS int LANGUAGE sql STRICT IMMUTABLE
AS $$
SELECT CASE WHEN $1 > interval '0' THEN 1 WHEN x < interval '0' THEN -1
ELSE 0 END
$$;
That works because a STRICT function won't even be called if any of it's
inputs are NULL.
> Is there a more obvious way to do sign(interval)? Would it be
> technically difficult to make it "just work"?
Actually, after looking at the code for interval_lt, all that needs to
happen to add this support is to expose interval_cmp_internal() as a
strict function. It already does exactly what you want.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-04-13 19:28:11 | Re: [GENERAL] pg_upgrade error regarding hstore operator |
Previous Message | Gianni Ceccarelli | 2016-04-13 18:54:56 | Re: sign function with INTERVAL? |
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2016-04-13 19:10:33 | Re: Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold < |
Previous Message | Kevin Grittner | 2016-04-13 19:08:49 | Re: [HACKERS] Re: pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold < |