Retrieve returning row of INSERT in PL/pgSQL

Question

I have a table called test with the columns id, data in a PostgreSQL database.

I have created a trigger AFTER INSERT called insert_test that launches a function of the same name.

The insert SQL from PHP is this:

insert into test (data) values ('try') returning 'variables' as temp;

How can I access resulting values in temp inside the PL/pgSQL function?

You might even use a `BEFORE` trigger ([one example](http://stackoverflow.com/a/22736148/939860)) to mess with the values *before* they are even stored and returned. — Erwin Brandstetter, Sep 12 '15 at 16:49

score 1 · Answer 1 · edited Sep 12 '15 at 16:40

This query is a bit ambiguous, so my reply will be generic. The result for the RETURNING clause is calculated dynamically and sent to the client immediately. So you cannot touch these values from PL/pgSQL. But all values from tables are available in trigger functions via record variables NEW or OLD - so you are able to recalculate any expression.

CREATE TABLE foo(a int, b int);

CREATE OR REPLACE FUNCTION foo_trig()
RETURNS TRIGGER AS $$
BEGIN
  RAISE NOTICE '<<%>>', NEW.a + NEW.b;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER foo_t AFTER INSERT ON foo
  FOR EACH ROW EXECUTE PROCEDURE foo_trig();

postgres=# INSERT INTO foo VALUES(10,20) RETURNING a + b;
NOTICE:  <<30>>
┌──────────┐
│ ?column? │
╞══════════╡
│       30 │
└──────────┘
(1 row)

Retrieve returning row of INSERT in PL/pgSQL

1 Answers1