Thread: Re: [HACKERS] 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.