Re: PL/pgSQL & OVERLAPS operator

"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