Обсуждение: 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