What is the difference between INSERT...RETURN and pl/pgsql RETURNING?
От | Adam Mackler |
---|---|
Тема | What is the difference between INSERT...RETURN and pl/pgsql RETURNING? |
Дата | |
Msg-id | 20140312164329.GA18712@scruffle.mackler.org обсуждение исходный текст |
Ответы |
Re: What is the difference between INSERT...RETURN and pl/pgsql
RETURNING?
Re: What is the difference between INSERT...RETURN and pl/pgsql RETURNING? |
Список | pgsql-novice |
(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
Вложения
В списке pgsql-novice по дате отправления: