Strange date difference problem in PL/pgSQL

Question

I have written a procedure that should increase salary for employee table according to days of each persons experience. The values for increase are in another table. Could someone tell me why it doesn't increase salary for employees working more than 3650 days?

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;

Table for increasing salaries looks like this:

 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

If something is not clear just ask me questions.

Edit

The table definitions are: For Employee:

     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            |

Indexes:

"employee_pkey" PRIMARY KEY, btree (empid)

For increases:

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

Indexes:

"increases_pkey" PRIMARY KEY, btree (id)

Looks to me like you are creating your own nested-loop implementation just to perform an update involving a range-query. Why ? — wildplasser, Oct 16 '11 at 10:51
I thought that there is no other way when the range data are located in another table which isn't connected to the first one? Can I do it differently? — Gabriel Filipiak, Oct 16 '11 at 11:08
Yes, you can do a "UPDATE table1 SET salary = some_expression containing table1_and_table2 WHERE some_expression to match the right_row FROM table2_to_table1's rows" — wildplasser, Oct 16 '11 at 11:26
OK so maybe it is a better way :) But I am still wondering why the procedure above does not work? :) — Gabriel Filipiak, Oct 16 '11 at 11:38
My gut feeling is that either NULLS are involved, or your types are wrong. (date1 - date2) yields an interval, see the fine manual. Please show us the relevant table definitions. — wildplasser, Oct 16 '11 at 11:43
@wildplasser: substracting two dates yields an **integer**. Substracting timestamps yields an interval. — Erwin Brandstetter, Oct 17 '11 at 12:04

score 1 · Answer 1 · answered Oct 16 '11 at 14:22

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,'lutser@nocorp.com' , '1939-01-01', 'Lutser', '2011-09-30' , 'Kleine' , True, 100.0 )
, (2,'lutser@nocorp.com' , '1949-01-01', 'Prutser', '2011-10-01' , 'Grote' , True, 200.0 )
, (3,'lutser@nocorp.com' , '1959-01-01', 'Klutser', '2011-10-01' , 'Grote' , True, 200.0 )
, (4,'lutser@nocorp.com' , '1969-01-01', 'Glutser', '2011-10-01' , 'Grote' , True, 200.0 )
, (5,'lutser@nocorp.com' , '1979-01-01', 'Brutser', '2011-10-01' , 'Grote' , True, 200.0 )
, (6,'lutser@nocorp.com' , '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 ;

Casting between intervals and integers can be painful. Above I solved this by multiplying the int with a 1day interval. Now it is up to you to embed this fragment in a procedure / function.

unfortunately it did not work. It still works the same as previous — Gabriel Filipiak, Oct 17 '11 at 12:27
Probably because I (it's a habit) replaced the DATE by a TIMESTAMP. See Erwin's comment about the resulting types for each operation. — wildplasser, Oct 17 '11 at 15:13

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

A simple UPDATE should do:

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;

You don't need a plpgsql function for this.
I would advise you mark rows when they get their raise (in the same query) so you don't raise multiple times by accident.

Edit:

Here is a plpgsql function doing the same, as you asked for it. It returns the number of employees who got a raise.

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 17 '11 at 12:22

Erwin Brandstetter

605,456
145
1,078
1,228

Thx for you help, you see I am looking for examples that need to be done by stored procedure or can't be dane using a single sql query. I had to query once and then on the retrieved data do something else. The aim is to justify using dynamic sql in stored procedures intead of the program code. Can you or anyone help me? – Gabriel Filipiak Oct 17 '11 at 13:24
and this code has the same problem as mine it updates every row with 100 salary increase, which is not good. – Gabriel Filipiak Oct 17 '11 at 13:40
@GabrielFilipiak: if your data above is correct, then this code certainly only raises those employees by 100 who joined between 3650 and 7299 days ago (that is 10 - 20 years). – Erwin Brandstetter Oct 17 '11 at 13:50
@GabrielFilipiak: I have hundrets of plpgsql functions. They are very useful for complex operations or when something needs to be done procedurally. You can to this with plpgsql, that's perfectly fine. But it's not necessary. – Erwin Brandstetter Oct 17 '11 at 13:52
ok good to know but can you provide me an example where it is necesary? – Gabriel Filipiak Oct 17 '11 at 14:08
@GabrielFilipiak: you need it for triggers. Or something like this: http://stackoverflow.com/questions/7711432/how-to-set-value-of-composite-variable-field-using-dynamic-sql/7782839#7782839. Or for encapsulating a longer sequence of SQL commands. Or fpor a number of other reasons. More info [in the manual here](http://www.postgresql.org/docs/9.1/interactive/plpgsql-overview.html#PLPGSQL-ADVANTAGES). – Erwin Brandstetter Oct 17 '11 at 14:23
@GabrielFilipiak: Here is another example where plpgsql is clearly the best solution: http://stackoverflow.com/questions/7791361/sql-to-return-list-of-fields-containing-non-null-data/7797677#7797677 – Erwin Brandstetter Oct 19 '11 at 01:12

Strange date difference problem in PL/pgSQL

Edit

2 Answers2

Edit: