Is there any utility to update the table whenever text file gets changed?

On Thu, Sep 14, 2006 at 03:41:06AM -0700, Dhanaraj M wrote:

Is there any utility in postgresql which can do the following?

Moving to pgsql-general, which is the appropriate list for this.

The utility must update the table whenever there is any change in the
text file.
COPY command helps to do that, though this is not straight forward.
Can it be automated?

There's nothing in the database that could do this directly. You'd need
some kind of code at the OS level that would watch the file for changes
and then take appropriate action. Any number of languages would be
capable of this. Also note that you should probably do the copy into
something other than the live table in case something goes wrong. You
can then do:

BEGIN;
DROP TABLE real_table;
ALTER TABLE fake_table RENAME real_table;
COMMIT;
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Jim C. Nasby wrote:

On Thu, Sep 14, 2006 at 03:41:06AM -0700, Dhanaraj M wrote:

The utility must update the table whenever there is any change in the
text file.
Can it be automated?

There's nothing in the database that could do this directly.

I've seen examples where someone did this with DBI_link or some
derivative thereof - where when the table changes queries instantly
see the modified result..

DBI-Link documentation mentions that CSV and ps and Google
output can be used:
http://fetter.org/DBI-Link.pdf#search=%22dbi_link%20%22
so I'm guessing if he can write a DBI-like adapter for
the text file in question, this would work for him too.

On Thu, Sep 14, 2006 at 03:41:06 -0700,
Dhanaraj M <Dhanaraj.M@Sun.COM> wrote:

Is there any utility in postgresql which can do the following?

The utility must update the table whenever there is any change in the
text file.
COPY command helps to do that, though this is not straight forward.
Can it be automated?

You would either need to have the application which changes the text file
do soemthing or have another program watching the text file to see when
it changes and then take action. You probably don't want to use COPY as
that essentially does inserts, not updates.