Re: BUG #14350: VIEW with INSTEAD OF INSERT TRIGGER and COPY. Missing feature or working as designed.

On Mon, Oct 3, 2016 at 10:53 PM, <maxim(dot)boguk(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14350
> Logged by: Maksym Boguk
> Email address: maxim(dot)boguk(at)gmail(dot)com
> PostgreSQL version: 9.5.4
> Operating system: Linux
> Description:
>
> During developing the database structure migration with maximum
> compatibility for an outside application code (a lot of views with instead
> of triggers to transparently restructure underlying data), I found that one
> critical (for me) feature missing.
>
> I expected that I should be possible to COPY directly into a VIEW with
> INSTEAD OF INSERT trigger on it.
> But reality bite me again.
>
> Test case:
>
> create table ttt(id serial, name text);
> create view ttt_v AS select ''::text AS str;
> CREATE FUNCTION tf_ttt() RETURNS trigger AS $tf_ttt$
> BEGIN
> INSERT INTO ttt (name) VALUES (NEW.str);
> RETURN NULL;
> END;
> $tf_ttt$ LANGUAGE plpgsql;
> CREATE TRIGGER t_ttt_v INSTEAD OF INSERT ON ttt_v FOR EACH ROW EXECUTE
> PROCEDURE tf_ttt();
> COPY ttt_v FROM stdin;
> Some string
> Another string
> \.
> ^C
>
> ERROR: cannot copy to view "ttt_v"
>
> Unfortunately application use COPY to batch load in lot places.
> Is this a bug? Missing feature? Work as designed?
>
> PS: if it had been already discussed - sorry, I tried to search mail list
> archive but found nothing relevant.
>

I think currently there is no handling of INSTEAD of triggers in the copy
functionality.

It didn't seem difficult to the support the same, until unless there are any
problems for complext queries, so after adding the INSTEAD of triggers
check and calling the ExecIRInsertTriggers function, the Copy is also
working for the view.

Attached is a POC patch of the same. I didn't checked all the possible
scenarios.

Regards,
Hari Babu
Fujitsu Australia