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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
