Обсуждение: Instead trigger on a view to update base tables ?

Поиск
Список
Период
Сортировка

Instead trigger on a view to update base tables ?

От
"Day, David"
Дата:
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





Re: Instead trigger on a view to update base tables ?

От
"David G. Johnston"
Дата:
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.