5

I've got a table that describes some relation between two items. The items are referenced by ID. I'd like to create a trigger, so whenever you create a row (a,b), a symmetrical row (b,a) is created. I've tried creating after trigger, but it exceed stack depth, because it called itself on insert it had within itself. What is the proper way to do it?

Table would look somewhat like this:

CREATE TABLE MY_RELATION(
ID SERIAL,
THING1 INT NOT NULL,
THING2 INT NOT NULL,
PRIMARY KEY (ID)
);

CREATE TABLE MY_THING(
ID SERIAL,
VAL INT NOT NULL
PRIMARY KEY(ID)
);
ALTER TABLE MY_RELATION ADD FOREIGN KEY (THING1) REFERENCES MY_THING(ID);
ALTER TABLE MY_RELATION ADD FOREIGN KEY (THING2) REFERENCES MY_THING(ID);
1
  • 1
    @MatthewRock Shouldn't you have a UNIQUE constraint on (thing1, thing2) as well (or even to replace the existing PK)? Feb 2 '16 at 20:10
5

The right way to do this will probably be to have your application INSERT via a stored procedure (or in Postgres a function).

If stored procedures are not an option, create a view and rewrite inserts against that view so that they affect some other table.

 CREATE TABLE t (
     a integer,
     b integer
 );
 CREATE VIEW v AS SELECT t.a, t.b FROM t;
 CREATE RULE mirror AS
    ON INSERT TO v DO INSTEAD
    INSERT INTO t (a, b) VALUES (new.a,new.b), (new.b,new.a);

Incidentally, using a view will allow you to enforce your A-B, B-A pairing by also rewriting UPDATEs and DELETEs using other rules.

4

The session information function pg_trigger_depth() would solve your problem with trigger recursion. Requires Postgres 9.2 or later. The manual:

current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger)

Best used in a WHEN clause to CREATE TRIGGER:

CREATE OR REPLACE FUNCTION my_relation_mirror_things()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO my_relation (thing1, thing2) VALUES (NEW.thing2, NEW.thing1);
   RETURN NEW;
END
$func$;
CREATE TRIGGER trg_insbef
BEFORE INSERT ON my_relation
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE my_relation_mirror_things();

Starting with Postgres 11, use the more appropriate syntax:

...
EXECUTE FUNCTION my_relation_mirror_things();

Related:

db<>fiddle here
Old sqlfiddle

A rule (like @Chris provided) is another way. Generally triggers are simpler to manage than rules, though.

2
  • The downside to this approach, of course, is an insert on your table caused by a (different) trigger won't create the duplicate row. Feb 2 '16 at 21:58
  • @ChrisHarrington: True, but INSERT via trigger is not a common thing and only possible if a privileged user creates such a trigger. A view with rule is subject to a similar downside: Writing to the table directly circumvents the rule. Feb 3 '16 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.