auto-updatable view work just for postgresql-9.3 and above (for other version you still need to define DELETE/UPDATE trigger).
what i see is we just trying to have a work around either with BEFORE/AFTER trigger or with auto-updatable view
in stright forwad/normale way is just to define INSTEAD OF trigger on the master table that return NEW so it doesn't break RETURNING, and the actual tuples returned by the trigger wouldn't actually be inserted in the master table. after all, that what INSTEAD OF suppose to do.
tom lane : in partitioned table. normally (always), the data is stored in child tables (i know this not the case for inheritence) . any data inserted in master table is just an exception/error/design bug or this is just my case. what i mean is, if some one define master table as empty table (even without having INSTEAD OF trigger) is not wart (postgresql need to be more flexible, and let user define thier database architecture the way they like).
also, it would be nice that the example :
INSERT INTO cities (name, population, altitude, state)
VALUES ('New York', NULL, NULL, 'NY');
in the inheritence doc to work, (if we maked passes syntax error checking and planning phase) next step is to chose between rule and trigger (we already have instead of rule. we just need instead of trigger ) maybe this not a user defined one but implicitly.
-----Original Message-----
From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Robert Haas <robertmhaas@gmail.com>; Aliouii Ali <aliouii.ali@aol.fr>; pgsql-hackers <pgsql-hackers@postgresql.org>
Sent: Wed, Apr 1, 2015 8:01 pm
Subject: Re: [HACKERS] Tables cannot have INSTEAD OF triggers
On 1 April 2015 at 18:37, Andres Freund <andres@anarazel.de> wrote:
> On
2015-04-01 13:29:33 -0400, Tom Lane wrote:
>> As for partitioning, you could do
this:
>>
>> create table parent(...);
>> create table child(...)
inherits(parent); -- repeat as needed
>> create view v as select * from
parent;
>> attach INSTEAD OF triggers to v
>>
>> Now the application deals
only with v, and thinks that's the real
>> table.
>
> Sure, but that's just
making things unnecessarily hard. That then
> requires also defining
UPDATE/DELETE INSTEAD triggers which otherwise
> would just work.
>
No,
because as defined above the view v would be auto-updatable, so
updates and
deletes on v would just do the matching update/delete
on
parent.
Regards,
Dean