Re: What is the difference between INSERT...RETURN and pl/pgsql RETURNING?

Adam Mackler-4 wrote
> (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

Short answer:

RETURN sends the value out of the function -*back to the caller*.

RETURNING sends the value(s) - there can be more than one row returned using
this method - *back to the function.*

They serve different purposes and are not interchangeable.

Note that "RETURNING NEW" doesn't really make any sense though supposedly it
would work if you added an "INTO v_record_var" to the statement.  But that
is pointless in this case since RETURN does what you need.

Note the requirement to use "PERFORM * FROM ..." in pl/pgsql when you want
to execute a SELECT query and ignore its results.  There is no way to
"PERFORM" an "INSERT...RETURNING" since if you want to ignore the results
you simply drop the RETURNING.  You cannot drop the SELECT so a different
command word was needed for that situation.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/What-is-the-difference-between-INSERT-RETURN-and-pl-pgsql-RETURNING-tp5795721p5795726.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.