> On 31 May 2024, at 00:34, johnlumby@xxxxxxxxxxx wrote: > > On 5/30/24 4:56 PM, David G. Johnston wrote: (…) >> If anything is done it would have to be new syntax. >> >> > A much bigger task surely. > > On 5/30/24 5:19 PM, Adrian Klaver wrote: >> >> 2) Use INSTEAD OF triggers: >> >> > > Unfortunately the same functionality as in my example with the RULE is not supported for triggers on views : from the manual > INSTEAD OF triggers may only be defined on views, and only at row level; > > A RULE is essentially a statement-level operation which is what I need for this particular case. A row-level trigger would not work because it cannot "see" the query causing it to be fired, and also , (most importantly) is not fired at all if no rows match the original query, whereas a RULE is always in effect regardless of which rows are involved. before. I should add that the RULE I showed in my example is not the only RULE being used on this view - there are other conditional RULEs, and the combined effect is of being able to change the effect of the original statement into a set of new statements, one of which does what is needed. > > And if you are now inclined to say "well, maybe the application itself is poorly written and should be changed" - I would have to agree, but that is not mine to change. > > But I suppose that my next question, given what you both say about the RULE system being a dead-end, is whether there is any likelihood of supporting an INSTEAD OF trigger on a view at statement level? Maybe that stands more chance of going somewhere? What you’re attempting to do boils down to adding a virtualisation layer over the database. Several middleware products exist that provide data virtualisation, products that are accessed as a database (or as a web service, or both) that pass on queries to connected systems. The virtualisation layer rewrites those queries between the data sources and the user-visible virtual database connection and between generalised SQL and native dialects and languages. If existing products support your particular use-case though, namely rewriting operational data-storage queries to data-source specific DML statements and then report the correct number of affected rows back, I don’t know. However, an important reason that PG rules are deprecated (as I understand it) is that it is very hard to get right for generated columns, which are operations with side-effects (such as incrementing a sequence value, for example) that are included in those queries rewritten by the specified rules. I doubt that a data virtualisation layer would be able to solve that particular problem. Nevertheless, considering what path you’re on, they may be worth looking at. I don’t think there are any open-source initiatives (unfortunately), they’re all commercial products AFAIK, and not cheap. With a suitable use-case they can be rather valuable tools too though. Regards, Alban Hertroys -- Als je de draak wilt steken met iemand, dan helpt het, als die een punthoofd heeft.
