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?  (David Johnston <polobo@yahoo.com>)
Re: What is the difference between INSERT...RETURN and pl/pgsql RETURNING?  (David Johnston <polobo@yahoo.com>)
Список 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 по дате отправления:

Предыдущее
От: Daryl Foster
Дата:
Сообщение: Cannot insert to 'path' field using EclipseLink
Следующее
От: David Johnston
Дата:
Сообщение: Re: What is the difference between INSERT...RETURN and pl/pgsql RETURNING?