(cross-posted to Stack Overflow[1])
Hi:
Suppose the following:
----------------------
CREATE TABLE base_foo (size int);
CREATE VIEW foo AS SELECT * FROM base_foo;
CREATE FUNCTION insert_foo() RETURNS TRIGGER AS
$$BEGIN
INSERT INTO base_foo VALUES (NEW.size);
RETURN NEW;
END$$ language plpgsql;
CREATE TRIGGER insert_foo INSTEAD OF INSERT ON foo
FOR EACH ROW execute PROCEDURE insert_foo();
Observe This Behavior
---------------------
I can insert into my view:
INSERT INTO foo VALUES (2);
It works; no problem. Now, if I change the trigger function so that
instead of two statements, the second one `RETURN`, it is rather one
`INSERT` with a `RETURNING` clause, as so:
CREATE OR REPLACE FUNCTION insert_foo() RETURNS TRIGGER AS
$$BEGIN
INSERT INTO base_foo VALUES (NEW.size)
RETURNING NEW;
END$$ language plpgsql;
then an insert causes an error:
INSERT INTO foo VALUES (3);
ERROR: query has no destination for result data
CONTEXT: PL/pgSQL function insert_foo() line 2 at SQL statement
The Documentation
-----------------
Docs say of RETURN[2]:
> RETURN with an expression terminates the function *and returns the
> value* of expression to the caller.
Docs say of RETURNING[3]:
> The optional RETURNING clause causes INSERT to compute *and return
> value(s)* based on each row actually inserted.
My questions are two:
---------------------
1. What is the actual difference in meaning
between these two keywords in this context, explained in a way that
would permit me to predict the error?
2. What is the meaning of the error, _i.e._, since the trigger
function needs a value to return, and since the `INSERT` is the
final statement of the trigger function, why does the query have no
destination for the result data?
[1] http://stackoverflow.com/questions/22358149/what-is-the-difference-between-insert-returning-and-pl-pgsql-return
[2] http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#AEN58181
[3] http://www.postgresql.org/docs/9.3/interactive/sql-insert.html#AEN78354
Thank you,
--
Adam Mackler