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