PL/pgSQL中奇怪的日期差异问题

Question

我编写了一个程序，应根据每个人的经验天数增加员工表的工资。增加的值在另一个表中。有人能告诉我为什么工作时间超过 3650 天的员工的工资没有增加吗？

DECLARE
   row record;
   row2 record;
   dateDiff int;

BEGIN
  FOR row IN EXECUTE 'SELECT * FROM employee'
  LOOP
     FOR row2 IN SELECT * FROM increases
     LOOP
        dateDiff := now()::date - row.empjoindate;
        IF dateDiff> 3650 THEN
           RAISE NOTICE '%', dateDiff;
        END IF;
        IF dateDiff >= row2.employment_length_from
       AND dateDiff <  row2.employment_length_to THEN 
           UPDATE employee SET empsalary = empsalary + row2.pay_rise WHERE empid = row.empid;
        END IF;
     END LOOP;
  END LOOP;
END;

加薪表如下所示：

 id | employment_length_from | employment_length_to | pay_rise
----+------------------------+----------------------+----------
  2 |                   3650 |                 7300 |      200
  3 |                   7300 |                10950 |      400
  4 |                  10950 |                14600 |      600
  5 |                  14600 |                18250 |      800
  6 |                  18250 |                21900 |     1000
  1 |                      0 |                 3650 |      100

如果有不清楚的地方，请向我提问。

编辑

表定义是： 对于员工：

     Column      |            Type             | Modifiers
-----------------+-----------------------------+-----------
 empid           | integer                     | not null
 empemailaddress | character varying(255)      | not null
 empjoindate     | date                        |
 emplastname     | character varying(255)      |
 emplogintime    | timestamp without time zone |
 empname         | character varying(255)      |
 ispermanent     | boolean                     | not null
 empsalary       | double precision            |

指数：

"employee_pkey" PRIMARY KEY, btree (empid)

对于加薪：

         Column         |       Type       | Modifiers
------------------------+------------------+-----------
 id                     | integer          | not null
 employment_length_from | integer          |
 employment_length_to   | integer          |
 pay_rise               | double precision |

指数：

"increases_pkey" PRIMARY KEY, btree (id)

Answer 1

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp;

CREATE TABLE tmp.increases
    (id INTEGER NOT NULL PRIMARY KEY
    , employment_length_from INTEGER NOT NULL
    , employment_length_to INTEGER NOT NULL
    , pay_rise double precision
    );
INSERT INTO tmp.increases(id
             ,employment_length_from,employment_length_to,pay_rise)
VALUES
    (1 , 0 , 3650 , 100)
    ,(2 , 3650 , 7300 , 200)
    ,(3 , 7300 , 10950 , 400)
    ,(4 , 10950 , 14600 , 600)
    ,(5 , 14600 , 18250 , 800)
    ,(6 , 18250 , 21900 , 1000)
    ;

CREATE TABLE tmp.employee
    ( empid INTEGER NOT NULL
    , empemailaddress VARCHAR (255) not null
    , empjoindate DATE
    , emplastname VARCHAR (255)
    , emplogintime TIMESTAMP WITHOUT TIME ZONE
    , empname VARCHAR(255)
    , ispermanent BOOLEAN NOT NULL
    , empsalary DOUBLE PRECISION
    );
INSERT INTO tmp.employee(empid,empemailaddress,empjoindate,emplastname,emplogintime,empname,ispermanent,empsalary)
VALUES
(1,'[email protected]' , '1939-01-01', 'Lutser', '2011-09-30' , 'Kleine' , True, 100.0 )
, (2,'[email protected]' , '1949-01-01', 'Prutser', '2011-10-01' , 'Grote' , True, 200.0 )
, (3,'[email protected]' , '1959-01-01', 'Klutser', '2011-10-01' , 'Grote' , True, 200.0 )
, (4,'[email protected]' , '1969-01-01', 'Glutser', '2011-10-01' , 'Grote' , True, 200.0 )
, (5,'[email protected]' , '1979-01-01', 'Brutser', '2011-10-01' , 'Grote' , True, 200.0 )
, (6,'[email protected]' , '1989-01-01', 'Mutser', '2011-10-01' , 'Grote' , True, 200.0 )
    ;

SELECT * FROM tmp.employee ;

-- EXPLAIN ANALYZE
UPDATE tmp.employee emp
SET empsalary = empsalary + inc.pay_rise
FROM tmp.increases inc
    WHERE (now() - emp.empjoindate)
          >= inc.employment_length_from * '1 day'::interval
    AND (now() - emp.empjoindate)
         < inc.employment_length_to * '1 day'::interval
    ;
SELECT * FROM tmp.employee ;

区间和整数之间的转换可能会很痛苦。上面我通过将 int 乘以 1 天的间隔解决了这个问题。现在您可以将此片段嵌入到过程/函数中。

Answer 2

一个简单的 UPDATE 应该可以做到：

UPDATE employee e
SET    empsalary = empsalary + i.pay_rise
FROM   increases i
WHERE  (now()::date - e.empjoindate) >= i.employment_length_from
AND    (now()::date - e.empjoindate)  < i.employment_length_to;

您不需要为此使用 plpgsql 函数。
我建议您在行获得加注时对其进行标记（在同一查询中），这样您就不会意外多次加注。

编辑：

这是一个 plpgsql 函数，按照您的要求执行相同的操作。它返回获得加薪的员工人数。

CREATE OR REPLACE FUNCTION f_raise(OUT happy_employees integer) AS
$BODY$
BEGIN

UPDATE employee e
SET    empsalary = empsalary + i.pay_rise
FROM   increases i
WHERE  (now()::date - e.empjoindate) >= i.employment_length_from
AND    (now()::date - e.empjoindate)  < i.employment_length_to;

GET DIAGNOSTICS happy_employees = ROW_COUNT;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;
COMMENT ON FUNCTION f_raise() IS 'Gives employees who deserve it a raise.
Returns number of happy employees.'