sql

CREATE OR REPLACE FUNCTION eligible_entities_onchange()

RETURNS TRIGGER AS $eligible_entities$

BEGIN

IF (TG_OP = 'DELETE') THEN

DELETE FROM eligible_entities

WHERE old.index_request.id = eligible_entities.id ;

ELSIF (TG_OP = 'UPDATE') THEN

--1) delete

DELETE FROM eligible_entities

WHERE old.index_request.id = eligible_entities.id ;

--2) insert

INSERT INTO eligible_entities 

  SELECT 

    new.id

    , regexp_split_to_table(new.index_request.eligible_roles, E';;')

    , null as eligible_groups

    , null as eligible_employees

        , null as assignees

        , null as candidates

      FROM index_request;

INSERT INTO eligible_entities 

  SELECT 

    new.id

    , null as eligible_roles

    , regexp_split_to_table(new.index_request.eligible_groups, E';;')

    , null as eligible_employees

        , null as assignees

        , null as candidates

      FROM index_request;

INSERT INTO eligible_entities 

  SELECT 

    new.id

    , null as eligible_roles

    , null as eligible_groups

    , regexp_split_to_table(new.index_request.eligible_employees, E';;')

        , null as assignees

        , null as candidates

      FROM index_request;

INSERT INTO eligible_entities 

  SELECT 

    new.id

    , null as eligible_roles

    , null as eligible_groups

    , null as eligible_employees

        , regexp_split_to_table(new.index_request.assignees, E';;')

        , null as candidates

      FROM index_request;

INSERT INTO eligible_entities 

  SELECT 

    new.id

    , null as eligible_roles

    , null as eligible_groups

    , null as eligible_employees

        , null as assignees

        , regexp_split_to_table(new.index_request.candidates, E';;')

      FROM index_request;

    /*(id, eligible_roles, eligible_groups, eligible_employees, assignees, candidates)

VALUES (new.id, regexp_split_to_table(new.index_request.eligible_roles, E';;'), null, null, null, null);*/

/*INSERT INTO eligible_entities (id, eligible_roles, eligible_groups, eligible_employees, assignees, candidates)

VALUES (new.id, null, regexp_split_to_table(new.index_request.eligible_groups, E';;'), null, null, null);

INSERT INTO eligible_entities (id, eligible_roles, eligible_groups, eligible_employees, assignees, candidates)

VALUES (new.id, null, null, regexp_split_to_table(new.index_request.eligible_employees, E';;'), null, null);

INSERT INTO eligible_entities (id, eligible_roles, eligible_groups, eligible_employees, assignees, candidates)

VALUES (new.id, null, null, null, regexp_split_to_table(new.index_request.assignees, E';;'), null);

INSERT INTO eligible_entities (id, eligible_roles, eligible_groups, eligible_employees, assignees, candidates)

VALUES (new.id, null, null, null, null, regexp_split_to_table(new.index_request.candidates, E';;'));*/

ELSIF (TG_OP = 'INSERT') THEN

  INSERT INTO eligible_entities 

  SELECT 

    new.id

    , regexp_split_to_table(new.index_request.eligible_roles, E';;')

    , null as eligible_groups

    , null as eligible_employees

        , null as assignees

        , null as candidates

      FROM index_request;

INSERT INTO eligible_entities 

  SELECT 

    new.id

    , null as eligible_roles

    , regexp_split_to_table(new.index_request.eligible_groups, E';;')

    , null as eligible_employees

        , null as assignees

        , null as candidates

      FROM index_request;

INSERT INTO eligible_entities 

  SELECT 

    new.id

    , null as eligible_roles

    , null as eligible_groups

    , regexp_split_to_table(new.index_request.eligible_employees, E';;')

        , null as assignees

        , null as candidates

      FROM index_request;

INSERT INTO eligible_entities 

  SELECT 

    new.id

    , null as eligible_roles

    , null as eligible_groups

    , null as eligible_employees

        , regexp_split_to_table(new.index_request.assignees, E';;')

        , null as candidates

      FROM index_request;

INSERT INTO eligible_entities 

  SELECT 

    new.id

    , null as eligible_roles

    , null as eligible_groups

    , null as eligible_employees

        , null as assignees

        , regexp_split_to_table(new.index_request.candidates, E';;')

      FROM index_request;

/*INSERT INTO eligible_entities (id, eligible_roles, eligible_groups, eligible_employees, assignees, candidates)

VALUES (new.id, regexp_split_to_table(new.index_request.eligible_roles, E';;'), null, null, null, null);

INSERT INTO eligible_entities (id, eligible_roles, eligible_groups, eligible_employees, assignees, candidates)

VALUES (new.id, null, regexp_split_to_table(new.index_request.eligible_groups, E';;'), null, null, null);

INSERT INTO eligible_entities (id, eligible_roles, eligible_groups, eligible_employees, assignees, candidates)

VALUES (new.id, null, null, regexp_split_to_table(new.index_request.eligible_employees, E';;'), null, null);

INSERT INTO eligible_entities (id, eligible_roles, eligible_groups, eligible_employees, assignees, candidates)

VALUES (new.id, null, null, null, regexp_split_to_table(new.index_request.assignees, E';;'), null);

INSERT INTO eligible_entities (id, eligible_roles, eligible_groups, eligible_employees, assignees, candidates)

VALUES (new.id, null, null, null, null, regexp_split_to_table(new.index_request.candidates, E';;'));*/

END IF;

END;

$eligible_entities$ LANGUAGE plpgsql

;