Обсуждение: What is the difference between INSERT...RETURN and pl/pgsql RETURNING?

(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

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.

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

This is a false assumption.  The final statement of the block is the "END;"
(or, arguably, an implicit RETURN since one was not specified).  However you
want to define it the INSERT...RETURNING does not make its output available
as a return value, only RETURN is capable of doing that.  If you fail to
issue RETURN then either (not sure at the moment the circumstances for each
possibility) you will get an error when you reach the end of the function or
you will implicitly call "RETURN;" (i.e., return nothing).

Unlike some other languages the result of the last statement in a function
is not automatically used as a return value.  All returned values must be
explicitly sent.

David J.




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