PL/pgSQL & OVERLAPS operator

From: Tuo Pe <tuo_pe(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: PL/pgSQL & OVERLAPS operator
Date: 2010-03-23 09:59:28
Message-ID: 60051.53228.qm@web50308.mail.re2.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello!

I am teaching myself PL/pgSQL. I am trying to write a function that tests whether two time periods overlap. I want to test the function parameters against these two values in "overlaptest" table:

select * from overlaptest;
id | alku | loppu
----+---------------------+---------------------
1 | 2010-03-23 10:00:00 | 2010-03-23 12:00:00
(1 row)

I have written this function,

CREATE OR REPLACE FUNCTION TryOverlap(text, text) RETURNS boolean AS $$
DECLARE
ts_start timestamp with time zone := CAST ($1 AS TIMESTAMP WITH TIME ZONE);
ts_end timestamp with time zone := CAST ($2 AS TIMESTAMP WITH TIME ZONE);
alku timestamp with time zone;
loppu timestamp with time zone;
BEGIN
SELECT alku,loppu FROM overlaptest WHERE id = 1 INTO alku,loppu;
IF ((alku,loppu) OVERLAPS (ts_start,ts_end)) THEN
RETURN true;
END IF;
RETURN false;
END;
$$ LANGUAGE plpgsql;

However, it always seems to return the value false. What's the problem here?

I appreciate any help.

Tuo

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A. Kretschmer 2010-03-23 10:18:30 Re: PL/pgSQL & OVERLAPS operator
Previous Message josep porres 2010-03-23 09:43:16 Re: db error messages when I try to debug with pgadmin