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 <[email protected]>
To: Andres Freund <[email protected]>
Cc: Tom Lane <[email protected]>; Robert Haas <[email protected]>; Aliouii
Ali <[email protected]>; pgsql-hackers <[email protected]>
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 <[email protected]> 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
