On 4/13/16 1:36 PM, Daniel Lenski wrote:
Is there a good reason why the SIGN() function does not work with the
INTERVAL type? (It is only defined for numeric types.)
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
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
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
Sent via pgsql-hackers mailing list (email@example.com)
To make changes to your subscription: