Обсуждение: Instead trigger on a view to update base tables ?
In a view of three joined tables, I install a INSTEAD OF trigger fx on the view. The fx contains a list of felds/columnsvariable associated to each base tables. When an update operation occurs, I am successfully generating the target list of colums altered on Each base table. ( comparing OLD v NEW ) and attempting some dynamic sql generation in my trigger fx. I am taking the list of modified fields on the view, and attempting an update on appropriate base tables. In this sample case "language_preference" was modified on the view and should update the admn.user base table EXECUTE format(' UPDATE admin.user SET (%I) = ( SELECT %I FROM $1 ) WHERE id = $2)', USER_SETTING, USER_SETTING ) USING NEW, NEW.id; When this executes my exception handler generates "err syntax error at or near \"$1\" The formatted statement on my base table (admin.user ) that is throwing this is executing would be: UPDATE admin.user SET (language_preference) = ( SELECT language_preference FROM $1 ) WHERE id = $2)" Feel Like Im close but missing something fundamental. I also an update variant UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) Which I thought might be applicable. but still googling for sample implementation. Thanks for any guidance in this method or better methods to update the base tables. Regards Dave Day
EXECUTE format(' UPDATE admin.user SET (%I) = ( SELECT %I FROM $1 ) WHERE id = $2)', USER_SETTING, USER_SETTING )
USING NEW, NEW.id;
When this executes my exception handler generates "err syntax error at or near \"$1\"
The value after FROM must be an identifier and so cannot be parameterized. You have to use "%I" for that dynamic element as well - only $2 (which becomes $1) is valid to parameterize (though you could just do "%L" and drop the USING clause on the EXECUTE...)
David J.