Re: [HACKERS] Why no INSTEAD OF triggers on tables?
On 12/17/13, 4:53 AM, Robert Haas wrote: Well, I'm specifically thinking of master partition tables. In that case, we really want an INSTEAD OF trigger. /me scratches head. So, put a BEFORE trigger, and make it return NULL. Same effect, different notation. But it's not the same effect at all, that's the point: =# create view foov as select 1 as a; CREATE VIEW =# create function insteadof() returns trigger as $$ $# begin $# -- INSERT here $# return new; $# end $# $$ language plpgsql; CREATE FUNCTION =# create function before() returns trigger as $$ $# begin $# -- INSERT here $# return null; $# end $# $$ language plpgsql; CREATE FUNCTION =# create trigger t1 instead of insert on foov for each row execute procedure insteadof(); CREATE TRIGGER =# create trigger t2 before insert on bart for each row execute procedure before(); CREATE TRIGGER =# insert into foov values (1) returning *; a --- 1 (1 row) INSERT 0 1 local:marko=#* insert into bart values (1) returning *; a --- (0 rows) INSERT 0 0 Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why no INSTEAD OF triggers on tables?
On Tue, Dec 17, 2013 at 3:27 AM, Marko Tiikkaja ma...@joh.to wrote: On 12/17/13, 4:53 AM, Robert Haas wrote: Well, I'm specifically thinking of master partition tables. In that case, we really want an INSTEAD OF trigger. /me scratches head. So, put a BEFORE trigger, and make it return NULL. Same effect, different notation. But it's not the same effect at all, that's the point: =# create view foov as select 1 as a; CREATE VIEW =# create function insteadof() returns trigger as $$ $# begin $# -- INSERT here $# return new; $# end $# $$ language plpgsql; CREATE FUNCTION =# create function before() returns trigger as $$ $# begin $# -- INSERT here $# return null; $# end $# $$ language plpgsql; CREATE FUNCTION =# create trigger t1 instead of insert on foov for each row execute procedure insteadof(); CREATE TRIGGER =# create trigger t2 before insert on bart for each row execute procedure before(); CREATE TRIGGER =# insert into foov values (1) returning *; a --- 1 (1 row) INSERT 0 1 local:marko=#* insert into bart values (1) returning *; a --- (0 rows) INSERT 0 0 Ah, interesting point. I didn't realize it worked like that. That does seem like a mighty useful thing to be able to do. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why no INSTEAD OF triggers on tables?
On 12/16/2013 07:53 PM, Robert Haas wrote: So, put a BEFORE trigger, and make it return NULL. Same effect, different notation. NOT the same: Master partition table with BEFORE trigger: josh=# insert into a ( id, val ) values ( 23, 'test' ), ( 24, 'test'), (25,'test'); INSERT 0 0 ^^^ View with INSTEAD OF trigger: josh=# insert into a_v ( id, val ) values ( 23, 'test' ), ( 24, 'test'), (25,'test'); INSERT 0 3 ^^^ The difference here is that the INSTEAD OF trigger returns a rows-affected count, and the BEFORE trigger does not (it returns 0). Some drivers and ORMs, most notably Hibernate, check this rows-returned count, and error if they don't match the rows sent. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Why no INSTEAD OF triggers on tables?
Hackers, I've looked in the archives, but I can't find a reason why INSTEAD OF triggers were never enabled for tables. I'm interested in them in order to return a rowcount to JDBC for INSERTs into partitioned tables. Was there a technical obstacle, or is this just a TUIT issue? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why no INSTEAD OF triggers on tables?
Josh Berkus j...@agliodbs.com writes: I've looked in the archives, but I can't find a reason why INSTEAD OF triggers were never enabled for tables. What would that mean exactly? And how would you do the actual update when it came time to? Was there a technical obstacle, or is this just a TUIT issue? It sounds more like a requestor-hasnt-thought-it-through issue. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why no INSTEAD OF triggers on tables?
On 12/16/2013 04:22 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: I've looked in the archives, but I can't find a reason why INSTEAD OF triggers were never enabled for tables. What would that mean exactly? And how would you do the actual update when it came time to? Well, I'm specifically thinking of master partition tables. In that case, we really want an INSTEAD OF trigger. It seems a little silly that I need to create a separate view, and then an INSTEAD OF trigger on the view, in order to get a rows-updated count back from an INSERT which hits a partitioned table. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why no INSTEAD OF triggers on tables?
On Mon, Dec 16, 2013 at 9:16 PM, Josh Berkus j...@agliodbs.com wrote: On 12/16/2013 04:22 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: I've looked in the archives, but I can't find a reason why INSTEAD OF triggers were never enabled for tables. What would that mean exactly? And how would you do the actual update when it came time to? Well, I'm specifically thinking of master partition tables. In that case, we really want an INSTEAD OF trigger. /me scratches head. So, put a BEFORE trigger, and make it return NULL. Same effect, different notation. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
