PostgreSQL trigger not returning anything

Question

I have a PostgreSQL trigger on create that basically redirects inserts into sub-tables. Once I insert the record, I want to ABORT the request as to avoid duplicate data. The only way (that I know of) to do this is to return NULL in the trigger. The problem is that I need the record to be returned so I can get the ID. If I return NULL, I get ... NULL.

Any idea how I can have a trigger abort an operation while still returning something other than NULL?

Answer 1

Your question leaves room for interpretation. The way I understand it, you want the RETURNING clause of the INSERT command to return the value of the primary key generated by a sequence.

There are other ways to achieve this. Like using nextval() to get the next id from the sequence beforehand and insert the row with the id spelled out.
OR currval() / lastval() to get the most recently obtained value for for a sequence / any sequence in the current session. More in this related answer:
PostgreSQL next value of the sequences?

You could also use a RULE ... INSTEAD .. for this purpose.

But, to answer your question - if that is, in fact, your question: it can be done by using two triggers. One BEFORE, one AFTER INSERT. Both are fired in one transaction per definition, so the phantom row in your first table is never visible to anybody (except the triggers).

Demo:

CREATE TABLE x (
  id serial PRIMARY KEY  -- note the serial col.
 ,name text
);

CREATE TABLE y (
  id integer PRIMARY KEY
 ,name text
);


CREATE OR REPLACE FUNCTION trg_x_insbef()
  RETURNS trigger AS
$func$
BEGIN
  INSERT INTO y SELECT (NEW).*;  -- write to other table
  RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insbef
  BEFORE INSERT ON x
  FOR EACH ROW EXECUTE PROCEDURE trg_x_insbef();


CREATE OR REPLACE FUNCTION trg_x_insaft()
  RETURNS trigger AS
$func$
BEGIN
  DELETE FROM x WHERE id = NEW.id; -- delete row again.
  RETURN NULL;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insaft
  AFTER INSERT ON x
  FOR EACH ROW EXECUTE PROCEDURE trg_x_insaft();

Call in psql:

db=# INSERT INTO x (name) values('phantom') RETURNING id;
 id
----
  1
(1 row)

INSERT 0 1

db=# SELECT * FROM x;
 id | name
----+------
(0 rows)


db=# SELECT * FROM y;
 id |  name
----+---------
  1 | phantom
(1 row)