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);
4
  • Can you provide a dump of your table schema? – Chris Harrington Feb 2 '16 at 15:42
  • @ChrisHarrington It's somewhat like the one above. – MatthewRock Feb 2 '16 at 15:46
  • Please provide your version of Postgres and the trigger and trigger function you tried (even if it's not working). – Erwin Brandstetter Feb 2 '16 at 17:54
  • 1
    @MatthewRock Shouldn't you have a UNIQUE constraint on (thing1, thing2) as well (or even to replace the existing PK)? – ypercubeᵀᴹ 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 it in a WHEN clause to CREATE TRIGGER:

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

Related:

SQL Fiddle.

A rule (like @Chris provided) is another elegant solution. 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. – Chris Harrington 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. – Erwin Brandstetter 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.