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
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? |