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?

So you're trying to build a trigger on table X that inserts the data into Y and Z, prevents anything from being inserted into X, but returns some ID? — mu is too short, Oct 31 '11 at 00:29
Yes, I'm partitioning the data into inherited tables. So I don't want a duplicate row in the parent table. I basically want this process to be completely transparent. You deal with the parent table, the triggers do everything behind the scenes. Nothing changes from the outside looking in. — Binary Logic, Nov 01 '11 at 00:33

score 7 · Accepted Answer · edited May 23 '17 at 12:31

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)

Thanks! This is the exactly result I wanted. My concern is performance, adding a row and then deleting it. I know its not a big hit, just seems messy. It's not possible to do this any other way? Thanks for the help. — Binary Logic, Oct 31 '11 at 20:27
I am sure there are another ways, but that is what you asked for. I hinted to use nextval() in my answer. Did you see that? (Plus link.) That's how I might do it. The downside with `nextval()`: one more call to the server. So it depends on the circumstances which will be faster. My example is a workaround but should be perfectly safe to use. — Erwin Brandstetter, Oct 31 '11 at 21:02

PostgreSQL trigger not returning anything

1 Answers1

Linked