Search Postgresql Archives

Re: Why overlaps is not working

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> CREATE OR REPLACE FUNCTION f_v_same_day_overlaps(date,
>       date, date, date, out overlaps bool) as
> $_$
> SELECT (($3 between $1 and $2) or ($4 between $1 and $2));
> $_$ language sql;

Thank you.
In my application second and fourth parameters can be NULL which means 
forever.
So I tried the code:

CREATE OR REPLACE FUNCTION public.doverlaps(date,
        date, date, date, out bool) IMMUTABLE AS
$_$
IF $1 is NULL OR $3 IS NULL THEN
  RAISE EXCEPTION 'doverlaps: first or third parameter is NULL % %',$1,$3;
  END IF;

IF $2 is null and $4 is null THEN
  SELECT true;
  RETURN;
END IF;

IF $2 is null THEN
  SELECT $1<=$4;
  RETURN;
END IF;

IF $4 is null THEN
  SELECT $2>=$3;
  RETURN;
END IF;

SELECT ($3 between $1 and $2) or ($4 between $1 and $2);

$_$ language sql;

This causes error

ERROR: syntax error at or near "IF"
SQL state: 42601
Character: 109

So I changed code to

CREATE OR REPLACE FUNCTION public.doverlaps(date,
        date, date, date, out bool) IMMUTABLE AS
$_$
SELECT ($3 between $1 and coalesce($2, '99991231')) or
       (coalesce($4, '99991231') between $1 and coalesce($2, '99991231'));
$_$ language sql;

It this best solution ?
How many times this is slower than expression in where clause?

Andrus. 




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux