PostgreSQL – Returning data from the update view does not work?

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
);

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;

GRANT SELECT, INSERT ON person_details TO public;

CREATE FUNCTION t0ii_person_details() RETURNS trigger AS $$
DECLARE
 addr integer; 
BEGIN
 - Want at least a town to insert anything into table ad dress.
 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();

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)

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

CREATE FUNCTION t0ii_person_details() RETURNS trigger AS $$
DECLARE< br /> 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;< br />
 INSERT INTO person(first_name, family_name, address)
 VALUES (NEW.first_name, NEW.family_name, addr) 
 - here is the only change I made
 RETUR NING id INTO NEW.id;
 RETURN NEW;
END; $$LANGUAGE plpgsql SECURITY DEFINER;

I have two simple tables And address, inserted through the updatable view person_details.

Relations and triggers

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
);

Obviously, the owner has full permissions, but ordinary users can only include Information view access data:

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 updatable so that regular users can insert into the table below the view:

GRANT SELECT, INSERT ON person_details TO public;

I have defined a trigger to propagate INSERT to the table, and everything works fine:

< p>

CREATE FUNCTION t0ii_person_details() RETURNS trigger AS $$
DECLARE
 addr integer;
BEG IN
 - 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

A simple insert:

pfams=# INSERT INTO person_details (first_name, family_name, town) VALUES ('John','Doe','Eders');
INSERT 0 1< br />
pfams=# SELECT * FROM person_details;
 id | first_name | family_name | street | town
----+------------+ -------------+--------+--------
 1 | John | Doe | | Eders
 (1 row)

INSERT RETURNING id-No way

However, if I want to return the newly assigned id directly from the INSERT statement (from the serial, serial data type that generated it person table), then I can’t get anything:

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

(1 row)

INSERT 0 1

But The data is:

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

What am I missing here?

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

You are too close;) you just need to be in the trigger Update NEW.id.

CREATE FUNCTION t0ii_person_details() RETURNS trigger AS $$
DECLARE
 addr integer;
BEGIN< br /> - 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; $$LAN GUAGE plpgsql SECURITY DEFINER;