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