From: | Adam Mackler <postgresql(at)mackler(dot)org> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | What is the difference between INSERT...RETURN and pl/pgsql RETURNING? |
Date: | 2014-03-12 16:43:29 |
Message-ID: | 20140312164329.GA18712@scruffle.mackler.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2014-03-12 16:54:28 | Re: What is the difference between INSERT...RETURN and pl/pgsql RETURNING? |
Previous Message | Sehrope Sarkuni | 2014-03-12 13:18:34 | Re: Cannot insert to 'path' field using EclipseLink |