Re: File System Access:

On Wed, Sep 27, 2006 at 11:15:19AM -0400, Oisin Glynn wrote:
> All,
>
> I have a need to create/copy/delete files on my server from a function
> in PostreSql. I am running 8.x PostreSql on Windows 200x and my
> functions etc are currently only in PL/pgSQL. Is there a way to create
> files/copy them and delete them from PL/pgSQL or could i call an
> external bat file job from PL/pgSQL.

Nope, for that you need an untrusted language like pl/perlu.

> I had thought about having a table with a list of operations and then
> scheduling a job in the OS to call and execute them but these operations
> need to happen in real time.

This is a better approach. Have a daemon that permanently attaches to
the database and executes a LISTEN. Then when someone else adds a row
to the table, it executes a NOTIFY, the daemon will get told and can do
the work.

LISTEN/NOTIFY is pretty much instantaneous, and you have a backup plan
(if copy/create/update fails for some reason, leave the row in the
table and try again later).

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.