Returning from insert on view

Поиск
Список
Период
Сортировка
От Aleksey Chirkin
Тема Returning from insert on view
Дата
Msg-id BANLkTimw5F4MO--1HvNoA8AJcSjfomq4Lw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Returning from insert on view  (Dean Rasheed <dean.a.rasheed@gmail.com>)
Список pgsql-general
Hello!

I need your advice.
My problem is to ensure that the right returning from insert on the view.

For example, I have two tables:

CREATE TABLE country (id serial, nm text);
CREATE TABLE city (id serial, country_id integer, nm text);

And one view on table "city", which join table "county" and adds
country_nm column.

CREATE VIEW city_view AS
  SELECT city.id, city.nm, city.country_id, country.nm AS country_nm
    FROM city
    JOIN country ON city.country_id = country.id;

I have two options for ensuring the returning from insert operation on view:

1) Create rule:

CREATE RULE ins AS ON INSERT
  TO city_view
  DO INSTEAD
  INSERT INTO city (nm, country_id) VALUES (NEW.nm, NEW.country_id)
    RETURNING id, nm, country_id,
      (SELECT nm FROM country WHERE id = city.country_id) AS country_nm;

2) Create trigger on view (for PostgreSQL 9.1):

CREATE FUNCTION city_view_insert()
  RETURNS trigger AS
$BODY$
BEGIN
  INSERT INTO city
    ( nm, country_id )
    VALUES ( NEW.nm, NEW.country_id )
    RETURNING id INTO NEW.id;

  SELECT * INTO NEW FROM city_view WHERE id = NEW.id;

  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;

CREATE TRIGGER on_insert INSTEAD OF INSERT  ON city_view
  FOR EACH ROW
  EXECUTE PROCEDURE city_view_insert();

It looks like a trick, and slows the insert, but it looks better and
avoids the complexities in returning.

Perhaps there is another way (may be a trick) to ensure the returning
from the insert on the view, without a manual compilation of the
returning columns?

Regards,
Aleksey

В списке pgsql-general по дате отправления:

Предыдущее
От: Carl von Clausewitz
Дата:
Сообщение: Need suggestion
Следующее
От: Pete Chown
Дата:
Сообщение: Re: Consistency of distributed transactions