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