Instead trigger on a view to update base tables ?

Started by Day, Davidover 7 years ago2 messagesgeneral
Jump to latest
#1Day, David
dday@redcom.com

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

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: Day, David (#1)
Re: Instead trigger on a view to update base tables ?

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.