Re: Triggers on Foreign Tables

On 04/04/2013 04:28 PM, Robert Lefkowitz wrote:
> Not sure if this is a feature request or a bug report.
>
> I'm trying to use Foreign Tables for a variety of things and it is useful to have a foreign table which appears to be
read/write.
>
> Having set one up, I can select data from it.   However, I can't insert, update or delete.
>
> No worries, thinks I.  I'll just create an   INSTEAD OF TRIGGER -- and handle the updates that way.
>
> However, one can't create a TRIGGER (in PosgreSQL version 9.2) on a foreign table -- the error message is:  not a
tableor view. 
>
> My current workaround is to create a view which is defined as   CREATE VIEW viewOnForeignTable AS SELECT * FROM
foreignTable
>
> I can then create INSTEAD OF TRIGGERs on viewOnForeignTable, as well as SELECT from viewOnForeignTable -- so that
becomesthe canonical interface. 
>
> However, it seems like it should be just as acceptable to apply the INSTEAD OF TRIGGERs to the FOREIGN TABLE .
>
> Should I consider this a bug (that I can't create aTRIGGER on a foreign table?) or a feature request (to allow
creatingTRIGGERs on foreign tables)? 

FYI, 9.3 will have update/insert/delete depending on the capabilities of
the underlying Foreign Data Wrapper:

http://www.depesz.com/2013/03/17/waiting-for-9-3-support-writable-foreign-tables/

Not sure if that fills your need?

You could file a feature request but new features are not added to minor
releases, so you will not see it until a later major release. At a guess
9.4+.

>
> Thanks,
>
> r0ml
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com