Instead trigger on a view to update base tables ?

Поиск
Список
Период
Сортировка
От Day, David
Тема Instead trigger on a view to update base tables ?
Дата
Msg-id a2a2a93605234ba9a984091de2924e9a@exch-02.redcom.com
обсуждение исходный текст
Ответы Re: Instead trigger on a view to update base tables ?  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-general
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





В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Immutable function WAY slower than Stable function?
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Instead trigger on a view to update base tables ?