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

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

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to