从可更新的视图返回数据不起作用？ [英] RETURNING data from updatable view not working?

问题描述

我有两个简单的表 person 和 address ，其中的插入内容通过可更新的视图 person_details 。

I have two simple tables person and address with inserts going through an updatable view person_details.

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 obviously has full permissions, but regular users can only access the data through a view which includes details 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;

视图是可更新的，因此普通用户可以 INSERT 插入视图基础的表中：

The view is updatable such that regular users can INSERT into the tables underlying the view:

GRANT SELECT, INSERT ON person_details TO public;

我已经定义了传播 INSERT 进入表，一切正常：

I have defined the trigger to propagate the INSERT to the tables and all works just 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

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

插入返回ID-不正确

但是，如果我要返回新分配的 id （源自 person 直接从 INSERT 语句从序列（序列数据类型）生成的表中，那么我什么也没得到返回：

INSERT RETURNING id - not ok

However, if I want to return the newly assigned id (originating from the person table where it is generated from a sequence, serial data type) directly from the INSERT statement, then I get nothing back:

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

推荐答案

您是如此亲密;）您只需要更新 NEW.id 。

You were so close ;) you only need to update NEW.id in your trigger.

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;

这篇关于从可更新的视图返回数据不起作用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！