PostgreSQL 检查先前记录的元素

Question

我需要检查以前记录的元素以确保我查询的日期不在结束日期和开始日期前 7 天之间的特定范围内。我有以下代码:

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;

所以基本上，我需要检查以下内容:

• 如果查询日期在起止日期之间

• 如果查询日期是开始日期开始前7天

• 如果查询日期在结束日期和开始日期之间并返回与该日期关联的 ID。

在大多数情况下，我的函数似乎工作正常，但有些情况似乎给我 0 个结果(当总是应该有 1 个结果时)我的函数中是否缺少某些东西？我不确定最后一个 if 语句。也就是说，尝试检查从以前的记录结束日期到当前记录的开始日期(有 7 天的间隔)

编辑:没有日期重叠。

Answer 1

^{编辑:删除了关于 RETURN NEXT 的部分 - 我误读了那里的问题。}
无法按照您的方式工作。 window function不能那样称呼。您的记录变量 r 就像 FOR 循环中的内置游标。只有结果的当前行在循环内可见。您必须集成 window function lag()它进入初始 SELECT。

但由于无论如何您都在以匹配的顺序遍历行，因此您可以采用另一种方式。

考虑这个很大程度上重写的例子。在第一个违规行返回:

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;

更多提示

• 为什么 $1 的别名？您已经在声明中将其命名为 _day。坚持下去。
• 一定要知道 PostgreSQL 如何处理 case in identifiers . (我只使用小写字母。)
• 您可以从日期中添加/减去整数(以天为单位)。