PostgreSQL检查以前的记录元素 [英] PostgreSQL checking a previous record's element

问题描述

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

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

如果查询日期为7开始日期之前的日期

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.

在大多数情况下，我的函数似乎工作正常，但有些情况似乎给了我0结果（当应该总是有1个结果）在我的函数中有什么遗漏吗？我是iffy关于最后一个if语句。也就是说，试图从以前的记录结束日期检查当前记录的开始日期（7天差距）

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)

编辑：没有日期重叠。

推荐答案

^{编辑：删除关于RETURN NEXT的部分 - 我误读了那个问题。}

不能按照你的方式工作。不能像这样调用窗口功能。您的记录变量 r 就像在 FOR 循环中的内置游标。结果的当前行只在循环中可见。您必须将窗口函数 lag（） 它进入初始 SELECT 。

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

更多提示

• 为什么 $ 1 的别名？您已经在声明中命名了 _day 。坚持下去。


• 确保了解PostgreSQL如何处理标识符中的大小写。 （我只使用小写字母。）


• 您可以从日期添加/减去整数（天）。



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.

这篇关于PostgreSQL检查以前的记录元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！