Re: sign function with INTERVAL?

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