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/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
On Tue, Aug 7, 2018 at 12:09 PM, Day, David <dday@redcom.com> wrote:
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.