Skip site navigation (1) Skip section navigation (2)

Re: Automatic export

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: "Daniel T(dot) Staal" <DStaal(at)usa(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Automatic export
Date: 2006-07-26 23:38:07
Message-ID: 20060726233807.GA62223@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Hal DavisonDate: 2006-07-26 23:47:48
Subject: Re: Lurking Wanna Be
Previous:From: Daniel T. StaalDate: 2006-07-26 19:27:01
Subject: Re: Automatic export

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group