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

From: maxim(dot)boguk(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14350: VIEW with INSTEAD OF INSERT TRIGGER and COPY. Missing feature or working as designed.
Date: 2016-10-03 11:53:22
Message-ID: 20161003115322.27234.5079@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

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.

Maksym

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2016-10-03 11:58:04 Re: pgbench - allow backslash continuations in \set expressions
Previous Message Peter Geoghegan 2016-10-03 11:51:54 Re: Tuplesort merge pre-reading

Browse pgsql-bugs by date

  From Date Subject
Next Message ing.marco.colombo 2016-10-03 14:21:00 BUG #14351: Upsert not working in case of partitioned tables
Previous Message Huan Ruan 2016-10-03 08:28:46 Re: BUG #14319: Logical decoding dropping statements in subtransactions