PostgreSQL checking a previous record's elemen

Question

I need to check the previous record's element to make sure the date I query doesn't fall within a specific range between ending date and 7 days before starting date. I have the following code:

create or replace function eight (date) returns text as $$
declare
  r record;
  checkDate alias for $1;
begin
    for r in
    select * from periods
    order by startDate
  loop
    if (checkDate between r.startDate and r.endDate) then
      return q3(r.id);
    elsif (checkDate between (r.startDate - interval '7 days') and r.startDate) then
      return q3(r.id);
    elsif (checkDate between (lag(r.endDate) over (order by r.startDate)) and (r.startDate - interval '8 days')) then
      return q3(r.id);
    end if;
  end loop;
  return null;
end;
$$ language plpgsql;

So basically, I need to check for the following:

• If the query date is between the starting and ending dates

• If the query date is 7 days before the start of the starting date

• If the query date is between ending date and the starting date and return the id that is associated with that date.

My function seems to work fine in most cases, but there are cases that seem to give me 0 results (when there should always be 1 result) is there something missing in my function? I'm iffy about the last if statement. That is, trying to check from previous records ending date to current records starting date (with the 7 day gap)

EDIT: no dates overlap.

Answer 1

SET search_path='tmp';
DROP table period;
CREATE table period
        ( start_date DATE NOT NULL
        , end_date DATE
        );
INSERT INTO period(start_date ,end_date) VALUES
 ( '2012-01-01' , '2012-02-01' )
 , ( '2012-02-01' , '2012-02-07' )
 , ( '2012-03-01' , '2012-03-15' )
 , ( '2012-04-01' , NULL )
 , ( '2012-04-17' , '2012-04-21' )
        ;

DROP FUNCTION valid_date(DATE) ;
CREATE FUNCTION valid_date(DATE) RETURNS boolean
AS $body$
declare
        found boolean ;
        zdate ALIAS FOR $1;
begin
found = false;
SELECT true INTO found
        WHERE EXISTS (
        SELECT * FROM period p
        WHERE (p.start_date > zdate
                AND p.start_date < zdate + interval '7 day' )
        OR ( p.start_date < zdate AND p.end_date > zdate )
        OR ( p.start_date < zdate AND p.end_date IS NULL
                AND p.start_date >= zdate - interval '7 day' )
        )
        ;
if (found  = true) then
        return false;
else
        return true;
end if;
end;
$body$ LANGUAGE plpgsql;

\echo 2011-01-01:true
SELECT valid_date('2011-01-01' );
\echo 2012-04-08:false
SELECT valid_date('2012-04-08' );
\echo 2012-04-30:true
SELECT valid_date('2012-04-30' );

BTW: I really think that the required functionality should be implemented as a table constraint, imposed by a trigger function (that might be based on the above function).

Answer 2

^{Edit: Removed the part about RETURN NEXT - I had misread the question there.}
Doesn't work the way you have it. A window function cannot be called like that. Your record variable r is like a built-in cursor in a FOR loop. Only the current row of the result is visible inside the loop. You would have to integrate the window function lag() it into the initial SELECT.

But since you are looping through the rows in a matching order anyway, you can do it another way.

Consider this largely rewritten example. Returns at the first violating row:

CREATE OR REPLACE FUNCTION q8(_day date)
  RETURNS text AS
$BODY$
DECLARE
    r            record;
    last_enddate date;

BEGIN
FOR r IN
    SELECT *
       -- ,lag(r.endDate) OVER (ORDER BY startDate) AS last_enddate
       -- commented, because I supply an alternative solution
    FROM   periods
    ORDER  BY startDate
LOOP
    IF _day BETWEEN r.startDate AND r.endDate THEN
        RETURN 'Violates condition 1';  -- I return differing results
    ELSIF _day BETWEEN (r.startDate - 7) AND r.startDate THEN
        RETURN 'Violates condition 2';
    ELSIF _day BETWEEN last_enddate AND (r.startDate) THEN 
                                      -- removed "- 7 ", that is covered above
        RETURN 'Violates condition 3';
    END IF;

    last_enddate := r.enddate; -- remember for next iteration
END LOOP;

RETURN NULL;

END;
$BODY$ LANGUAGE plpgsql;

More hints

• Why the alias for $1? You named it _day in the declaration already. Stick to it.
• Be sure to know how PostgreSQL handles case in identifiers. ( I only use lower case.)
• You can just add / subtract integers (for days) from a date.

Answer 3

Are you sure that lag() will return you something? I'm pretty sure that this is out of context here. Given that rows from periods are selected in order, you can store the current startDate in a variable, and use it in the if statement of the next cycle.