Re: Returning from insert on view

On 1 June 2011 10:32, Aleksey Chirkin <a4irkin(at)gmail(dot)com> wrote:
> 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?
>

Selecting from the view at the end of the trigger will be slower, so
if performance is a factor it would be better to just select the
required columns from the underlying table, but I'm not aware of any
trick to avoid listing the columns.

The trigger has greater scope for flexibility and validation of the
input data - maybe country names are supplied, which the trigger could
validate and get the corresponding ids. Maybe both are supplied, and
the trigger could check they are consistent, etc...

For bulk operations the rule should out-perform the trigger, since it
is just a query rewrite (like a macro definition). However, there are
a lot more gotchas when it comes to writing rules. So the main
advantages of the trigger are that it is less error-prone, and it is
easier to write complex logic in a procedural language.

Regards,
Dean

> Regards,
> Aleksey
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>