Re: Automatic export

On Wed, Jul 26, 2006 at 03:27:01PM -0400, Daniel T. Staal wrote:
> On Wed, July 26, 2006 3:17 pm, Keith Worthington said:
> > I need to export several records of a single colum from a table in a
> > database everytime that table is updated.  The data needs to end up in a
> > text file. Can someone give me an idea on where to get started?  URL's to
> > relevant documentation would be appreciated.  TIA
>
> I'd start here:
> http://www.postgresql.org/docs/8.1/interactive/triggers.html
>
> Write a trigger on insert/update on that table, and have it export the
> records.

Beware that triggers that perform actions outside the database won't
have transactional semantics.  If you update a table and a trigger
writes to an external file and then the transaction rolls back, the
changes to the external file will remain.  If that could be a problem
then consider using LISTEN/NOTIFY instead.  Notifications are sent
only if a transaction commits, so you could have a rule or trigger
that sends notifications and another process that listens for them
and does whatever needs to be done.  A disadvantage is that this
mechanism might require bookkeeping to know which rows to process.

http://www.postgresql.org/docs/8.1/interactive/sql-listen.html
http://www.postgresql.org/docs/8.1/interactive/sql-notify.html
http://www.postgresql.org/docs/8.1/interactive/libpq-notify.html

--
Michael Fuhr