Trigger to create symmetrical rows

Question

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);

Answer 1

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.

Answer 2

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:

• How to prevent a PostgreSQL trigger from being fired by another trigger?

db<>fiddle here
_{Old sqlfiddle}

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