PLPGSQL Cascading Triggers?

Question

I am trying to create a trigger, so that when ever I add a new record it adds another record in the same table. The session field will only take values between 1 and 4. So when I add a 1 in session I want it to add another record but with session 3 blocked. But the problem is that it leads to cascading triggers and it inserts itself again and again because the trigger is triggered when inserted.

I have for example a simple table:

CREATE TABLE example
(
id      SERIAL PRIMARY KEY
,name   VARCHAR(100) NOT NULL
,session   INTEGER
,status VARCHAR(100)
);

My trigger function is:

CREATE OR REPLACE FUNCTION add_block() RETURNS TRIGGER AS $$

BEGIN

INSERT INTO example VALUES (NEW.id + 1, NEW.name, NEW.session+2, 'blocked');

RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

Trigger is:

CREATE TRIGGER add_block
AFTER INSERT OR UPDATE
ON example
FOR EACH ROW
EXECUTE PROCEDURE add_block();

I get error:

SQL statement "INSERT INTO example VALUES ( $1 +1,  $2 ,  $3 + 2, $4)"
PL/pgSQL function "add_block" line 37 at SQL statement

This error repeats itself so many times that I can't see the top.

How would I solve this?

EDIT:

CREATE TABLE block_rules
(
id      SERIAL PRIMARY KEY
,session   INTEGER
,block_session   INTEGER
);

This table holds the block rules. So if a new record is inserted into the EXAMPLE table with session 1 then it blocks session 3 accordingly by inserting a new record with blocked status in the same (EXAMPLE) table above (not block_rules). Same for session 2 but it blocks session 4.

The block_rules table holds the rules (or pattern) to block a session by. It holds

id | session | block_session
------------------------------
1  | 1       | 3
2  | 2       | 4
3  | 3       | 2

How would I put that in the WHEN statement of the trigger going with Erwin Branstetter's answer below?

Thanks

Answer 1

New answer to edited question

This trigger function adds blocked sessions according to the information in table block_rules.

I assume that the tables are linked by id - information is missing in the question.
I now assume that the block rules are general rules for all sessions alike and link by session. The trigger is only called for non-blocked sessions and inserts a matching blocked session.

Trigger function:

CREATE OR REPLACE FUNCTION add_block()
  RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO example (name, session, status)
    VALUES (NEW.name
           ,(SELECT block_session
             FROM   block_rules
             WHERE  session = NEW.session)
           ,'blocked');

    RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER add_block
AFTER INSERT -- OR UPDATE
ON example
FOR EACH ROW
WHEN (NEW.status IS DISTINCT FROM 'blocked')
EXECUTE PROCEDURE add_block();

---

Answer to original question

There is still room for improvement. Consider this setup:

CREATE OR REPLACE FUNCTION add_block()
  RETURNS TRIGGER AS
$BODY$
BEGIN

INSERT INTO example (name, session, status)
VALUES (NEW.name, NEW.session + 2, 'blocked');

RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;


CREATE TRIGGER add_block
AFTER INSERT -- OR UPDATE
ON example
FOR EACH ROW
WHEN (NEW.session < 3)
-- WHEN (status IS DISTINCT FROM 'blocked') -- alternative guess at filter
EXECUTE PROCEDURE add_block();

Major points:

• For PostgreSQL 9.0 or later you can use a WHEN condition in the trigger definition. This would be most efficient. For older versions you use the same condition inside the trigger function.

• There is no need to add a column, if you can define criteria to discern auto-inserted rows. You did not tell, so I assume that only auto-inserted rows have session > 2 in my example. I added an alternative WHEN condition for status = 'blocked' as comment.

• You should always provide a column list for INSERTs. If you don't, later changes to the table may have unexpected side effects!

• Do not insert NEW.id + 1 in the trigger manually. This won't increment the sequence and the next INSERT will fail with a duplicate key violation.
  id is a serial column, so don't do anything. The default nextval() from the sequence is inserted automatically.

• Your description only mentions INSERT, yet you have a trigger AFTER INSERT OR UPDATE. I cut out the UPDATE part.

• The keyword plpgsql doesn't have to be quoted.

Answer 2

OK so can't you just add another column, something like this:

ALTER TABLE example ADD COLUMN trig INTEGER DEFAULT 0;

CREATE OR REPLACE FUNCTION add_block() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.trig = 0 THEN
        INSERT INTO example VALUES (NEXTVAL('example_id_seq'::regclass), NEW.name, NEW.session+2, 'blocked', 1);
    END IF;
    RETURN NULL;
 END;
 $$ LANGUAGE 'plpgsql';

it's not great, but it works :-)

Answer 3

CREATE OR REPLACE FUNCTION add_block() RETURNS TRIGGER AS $$
BEGIN
    SET SESSION session_replication_role = replica;
    INSERT INTO example VALUES (NEXTVAL('example_id_seq'::regclass), NEW.name, NEW.session+2, 'blocked');
    SET SESSION session_replication_role = origin;
    RETURN NULL;
 END;
 $$ LANGUAGE 'plpgsql';