Обсуждение: PL/pgSQL & OVERLAPS operator

Hello!

I am teaching myself PL/pgSQL. I am trying to write a function that tests whether two time periods overlap. I want to
testthe 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

In response to Tuo Pe :
> Hello!
>
> I am teaching myself PL/pgSQL. I am trying to write a function that tests whether two time periods overlap. I want to
testthe 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?

You have alku and loppu as variable and as table-column, that's a bad
idea, maybe that's an error, i'm not sure.

Btw.: you can use the PERIOD-datatype:

11:16 < akretschmer> ??period
11:16 < pg_docbot_adz> For information about 'period' see:
11:16 < pg_docbot_adz> http://wiki.postgresql.org/wiki/RangeTypes
11:16 < pg_docbot_adz> http://pgfoundry.org/projects/temporal
11:16 < pg_docbot_adz> http://github.com/davidfetter/PostgreSQL-Temporal

And 9.0 contains a new feature: exclusion constraints:
http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

--- On Tue, 3/23/10, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:

> You have alku and loppu as variable and as table-column,
> that's a bad
> idea, maybe that's an error, i'm not sure.
>
> Btw.: you can use the PERIOD-datatype:

I will look into it. Thanks for the tip.

Tuo

"A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> In response to Tuo Pe :
>> 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?
> You have alku and loppu as variable and as table-column, that's a bad
> idea, maybe that's an error, i'm not sure.

Yeah --- that SELECT will result in no change to the variables, ie,
they'll still be NULL.  So the OVERLAPS always fails.

            regards, tom lane

--- On Tue, 3/23/10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Yeah --- that SELECT will result in no change to the
> variables, ie,
> they'll still be NULL.  So the OVERLAPS always fails.

Tom & Andreas, I thank you for your help. Renaming the variables solved the problem. :-)

Regards,
Tuo