Instead trigger on a view to update base tables ?

From: "Day, David" <dday(at)redcom(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Instead trigger on a view to update base tables ?
Date: 2018-08-07 19:09:03
Message-ID: a2a2a93605234ba9a984091de2924e9a@exch-02.redcom.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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/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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2018-08-07 19:16:49 Re: Instead trigger on a view to update base tables ?
Previous Message Tom Lane 2018-08-07 18:59:07 Re: Immutable function WAY slower than Stable function?