In a view of three joined tables, I install a INSTEAD OF trigger fx on the
view. The fx contains a list of felds/columns variable 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
