Re: Scheduling data input from tab delimited file via php

Keith Worthington wrote:
> Dylan Fogarty-MacDonald wrote:
>> Hi,
>>
>> I have a client comprising of a group of car dealerships. Thier
>> accounting software uploads via ftp a tab delimited file of their
>> updated stock whenever the stock is updated.
>>
>> What I need to do is execute a php scipt that imports data from the
>> file into the database at a regular interval, ie. every hour.
>>
>> Does postgres have an ability handle this in a different way, maybe by
>> removing the need to use php at all?
>>
>> Can postgres be scheduled for actions like this? Or will I need to
>> have the php file schedule the action itself?
>>
>> Basically, I need to know if postgres can be scheduled, and if it can
>> open/execute files on a server.
>>
>> Thankyou,
>> I hope this was written in an understandable way...
>>
>> -Dylan
>
> Dylan,
>
> We load files that are sent to us randomly. We did it by creating a
> shell script that among other things checks to see if a copy of itself
> is already running and pipes data into the sql COPY command. We also
> created a cron entry that launches the shell script every minute.
>
> HTH
>

For simply importing data, psql will work just fine.

One thing to be aware of is that if you are using a cron-activated
process to check for files the process may, depending on the server and
your script, see the file as available when it is still being uploaded
and eventually you may end up triggereing a process that fails or only
imports part of the data.

I've found pure-ftpd to be an excellent and secure FTP server and it has
a feature that allows you to trigger a process whenever a file upload
completes so you don't have to wait for cron to pick it up and you don't
worry about in-progress uploads.

Oh, and to answer the original question, no, PostgreSQL does not have
its own scheduler - that's the job of cron (or your OS's equivalent).

Cheers,
Steve