Add recurrent actions for defined period in Postgresql

Question

Beside of unique actions we need recurrent actions in our database. We wan't the user to be able to define a periodicity (all 1,2,3,.. years) and a period (e.g. from 2018 - to 2020) in a form. This data should be used to insert appropriat datasets for a defined action.

If the user chooses an annual periodicity starting from 2018 3 datasets (2018, 2019 and 2020) should be inserted in the actions table.

If the user chooses an biannual periodicity starting from 2018 only 2 datasets (2018 and 2020) should be inserted in the actions table.

The simplified table actions looks like this:

id serial not null
id_action integer
action_year integer
periodicity integer
from_ integer
to_ integer

I need a starting point for the sql statement.

score 1 · Answer 1 · answered Nov 30 '18 at 22:37

You should use generate_series(start, stop, step)

Annual:

=> select generate_series(2018,2020,1);
 generate_series 
-----------------
            2018
            2019
            2020
(3 rows)

Biannual:

=> select generate_series(2018,2020,2);
 generate_series 
-----------------
            2018
            2020
(2 rows)

score 0 · Accepted Answer · answered Dec 01 '18 at 23:54

I didn't knew the function generate_series() until now. Thanks to point me in that direction.

To get things running like I intended I need to use the generate_series() inside an Trigger function that is fired AFTER INSERT. After first running into troubles with recursive Trigger inserts I now have the problem, that my Trigger produces to many duplicate inserts (increasing with the choosen periodicity).

My table actions looks like this:

id serial not null
id_action integer
action_year integer
periodicity integer
from_ integer
to_ integer

My Trigger on the table:

CREATE TRIGGER tr_actions_recurrent
  AFTER INSERT
  ON actions
  FOR EACH ROW
  WHEN ((pg_trigger_depth() = 0))
  EXECUTE PROCEDURE actions_recurrent();

Here my trigger function:

CREATE OR REPLACE FUNCTION actions_recurrent()
  RETURNS trigger AS
$BODY$
BEGIN
 IF NEW.periodicity >0 AND NEW.action_year <= NEW.to_-NEW.periodicity THEN
 INSERT into actions(id_action, action_year,periodicity, from_, to_)
 SELECT NEW.id_action, y, NEW.periodicity, NEW.from_, NEW.to_
 FROM actions, generate_series(NEW.from_+NEW.periodicity,NEW.to_,NEW.periodicity) AS y;
 END IF;
 RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

When I'm doing an insert

INSERT INTO actions (id_action, action_year,periodicity,from_, to_)
VALUES (50,2018,4,2018,2028);

I get one row for action_year 2018, but 13 rows 2022 and 2026?? In my understanding, the IF-clause in the trigger-function should avoid such a repetitive execution.

Add recurrent actions for defined period in Postgresql

2 Answers2