OVERLAPS operator to check multiples rows

Question

Currently I am using the overlaps operator so that users can't insert dates that overlap. I created this function:

    CREATE OR REPLACE FUNCTION overlap(docent_medewerkerscode1 CHARACTER(7),  
lokaal1 TEXT, groep_groepsnaam1 TEXT, startdatum1 timestamp, einddatum1 
timestamp)

RETURNS TEXT AS $$
DECLARE
    resultaat TEXT;
    rec rooster%rowtype;
BEGIN 
    FOR rec IN SELECT groep_groepsnaam = groep_groepsnaam1 OR lokaal =      
lokaal1 OR docent_medewerkerscode = docent_medewerkerscode1 FROM rooster 
  LOOP
        IF(startdatum1, einddatum1) OVERLAPS (startdatum, einddatum) FROM  
rooster THEN
           --IF startdatum1 BETWEEN startdatum AND einddatum AND einddatum1 BETWEEN startdatum AND einddatum FROM rooster THEN
            resultaat = 'tijd overlapt';
            RAISE 'Niet mogelijk tijd komt overeen met ingeplande tijd';
        END IF;
  END LOOP;

RETURN resultaat;
END;
$$ LANGUAGE plpgsql;

The problem is that the overlap doesn't work on multiples rows.

(ERROR: query "SELECT (startdatum1, einddatum1) OVERLAPS (startdatum, einddatum) FROM rooster" returned more than one row)

I am looking for a way that let's the overlap check each row from the select query. Any of you guys that have an idea of what I can do?

As *always*, your version of Postgres, please. In the question. — Erwin Brandstetter, May 16 '15 at 13:16

score 0 · Accepted Answer · edited May 23 '17 at 11:58

The immediate cause for the error message is that the plpgsql IF statement expects a single boolean expression, while your code returns a whole set of boolean expressions.

IF(startdatum1, einddatum1) OVERLAPS (startdatum, einddatum) FROM  
rooster THEN

is an ugly short form of:

IF (SELECT(startdatum1, einddatum1) OVERLAPS (startdatum, einddatum)
    FROM  rooster) THEN

and simply invalid this way. You would have to agggregate or use EXISTS:

IF EXISTS (
   SELECT 1 FROM  rooster r
   WHERE  (startdatum1, einddatum1) OVERLAPS (r.startdatum, r.einddatum)
   ) THEN ...

More importantly, an exclusion constraint is almost certainly the better solution for your problem (requires Postgres 9.0+):

Thank you for advice, this will definitely help! – Falio May 16 '15 at 20:13 — Falio, May 16 '15 at 20:13

OVERLAPS operator to check multiples rows

1 Answers1