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
– Erwin Brandstetter, Commented May 16, 2015 at 13:16

Community · Accepted Answer · 2017-05-23 11:58:09Z

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+):

Collectives™ on Stack Overflow

OVERLAPS operator to check multiples rows

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related