PostgreSQL checking a previous record's element

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.

Why should be always 1 result ? If you are using if/elseif/elseif condition. If there is no date in table that suited to the conditions, null will be return — Anton
– Anton, Commented Apr 21, 2012 at 9:35
@Anton it is assumed there will always be a result (according to the spec), but I don't think my third if statement is working properly because with all my checks, it seems the ones that are failing (compared to expected results) are because of that statement — SNpn
– SNpn, Commented Apr 21, 2012 at 9:37
@ErwinBrandstetter doesn't really tell me, since this is uni work — SNpn
– SNpn, Commented Apr 21, 2012 at 12:06

Erwin Brandstetter · Accepted Answer · 2012-04-21 13:10:45Z

3

^{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.

edited Apr 21, 2012 at 13:10

answered Apr 21, 2012 at 12:48

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wildplasser Over a year ago

The alias is a way to use it on older versions (< 8.4?) , which did not have named parameters. BTW: why the loop? IMHO my solution with just an exist query is cleaner. Does not need window functions, either.

András Váczi · Accepted Answer · 2012-04-21 11:02:50Z

0

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.

answered Apr 21, 2012 at 11:02

András Váczi

3,02225 silver badges30 bronze badges

1 Comment

SNpn Over a year ago

i am not sure, that is why i said im iffy about the third if statement

wildplasser · Accepted Answer · 2012-04-21 12:21:35Z

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

Collectives™ on Stack Overflow

PostgreSQL checking a previous record's element

3 Answers 3

More hints

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

More hints

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related