Returning from insert on view

From: Aleksey Chirkin <a4irkin(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Returning from insert on view
Date: 2011-06-01 09:32:44
Message-ID: BANLkTimw5F4MO--1HvNoA8AJcSjfomq4Lw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pete Chown 2011-06-01 12:18:48 Re: Consistency of distributed transactions
Previous Message Carl von Clausewitz 2011-06-01 08:08:14 Need suggestion