RETURN data from updated view not working?

Question

I have two simple tables personand addresswith inserts going through the updated view person_details.

Relations and trigger

CREATE TABLE address (
  id          serial PRIMARY KEY,
  street      varchar,
  town        varchar NOT NULL
);

CREATE TABLE person (
  id          serial PRIMARY KEY,
  first_name  varchar,
  family_name varchar NOT NULL,
  address     integer REFERENCES address
);

The owner clearly has full permissions, but ordinary users can only access data through a view that includes data from both tables:

CREATE VIEW person_details AS
  SELECT p.id, p.first_name, p.family_name, a.street, a.town
  FROM person p
  LEFT JOIN address a ON a.id = p.address;

The view is updated so that regular users can INSERTin the tables underlying the view:

GRANT SELECT, INSERT ON person_details TO public;

I defined a trigger for spreading INSERTto tables, and everything works fine:

CREATE FUNCTION t0ii_person_details() RETURNS trigger AS $$
DECLARE
  addr  integer;
BEGIN
  -- Want at least a town to insert anything into table address.
  IF (NEW.town IS NOT NULL) THEN
    INSERT INTO address(street, town)
      VALUES (NEW.street, NEW.town)
      RETURNING id INTO addr;
  ELSE
    addr := NULL;
  END IF;

  INSERT INTO person(first_name, family_name, address)
    VALUES (NEW.first_name, NEW.family_name, addr);
  RETURN NEW;
END; $$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER t0ii_person_details
  INSTEAD OF INSERT ON person_details
  FOR EACH ROW EXECUTE PROCEDURE t0ii_person_details();

INSERT - ok

Simple insert:

pfams=# INSERT INTO person_details (first_name, family_name, town) VALUES ('John', 'Doe', 'Eders');
INSERT 0 1

pfams=# SELECT * FROM person_details;
 id | first_name | family_name | street |  town
----+------------+-------------+--------+--------
  1 | John       | Doe         |        | Eders
(1 row)

INSERT RETURNING id - not normal

, id ( person, , serial ) INSERT, :

pfams=# INSERT INTO person_details (first_name, family_name, town) VALUES ('Jim', 'Doe', 'Eders') RETURNING id;
 id
----

(1 row)

INSERT 0 1

:

pfams=# SELECT * FROM person_details;
 id | first_name | family_name | street |  town
----+------------+-------------+--------+--------
  1 | John       | Doe         |        | Eders
  2 | Jim        | Doe         |        | Eders
(2 rows)

?

pfams=# SELECT version();
                                               version
-----------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.6 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 32-bit

Answer 1

;) NEW.id .

CREATE FUNCTION t0ii_person_details() RETURNS trigger AS $$
DECLARE
  addr  integer;
BEGIN
  -- Want at least a town to insert anything into table address.
  IF (NEW.town IS NOT NULL) THEN
    INSERT INTO address(street, town)
      VALUES (NEW.street, NEW.town)
      RETURNING id INTO addr;
  ELSE
    addr := NULL;
  END IF;

  INSERT INTO person(first_name, family_name, address)
    VALUES (NEW.first_name, NEW.family_name, addr) 
    -- here is the only change I made
    RETURNING id INTO NEW.id;
  RETURN NEW;
END; $$ LANGUAGE plpgsql SECURITY DEFINER;