Thread: run shell command from pl/pgsql

Hello all,

I'm working on a pg database that should be able to run commands on
certain conditions. E.g. whenever a date is stored in column X of table
Y, an email should be sent to user Z. Creating a triggered function is
easy enough but how do I invoke the command? Also, I want to use
variables in the executed command, something like:

    m_address := ''me@myself.org'';
    m_subject := ''this is a test'';
    runcommand(''mail -s '' + m_subject + m_address);

I guess the answer is very obvious, but I couldn't find it. TIA for any
help!

--
Jules Alberts.

Jules Alberts wrote:
> Hello all,
>
> I'm working on a pg database that should be able to run commands on
> certain conditions. E.g. whenever a date is stored in column X of
> table Y, an email should be sent to user Z. Creating a triggered
> function is easy enough but how do I invoke the command? Also, I want
> to use variables in the executed command, something like:
>
>       m_address := ''me@myself.org'';
>       m_subject := ''this is a test'';
>       runcommand(''mail -s '' + m_subject + m_address);
>
> I guess the answer is very obvious, but I couldn't find it. TIA for
> any help!
>
> --
> Jules Alberts.
>
You can't do this from pl/pgsql, or any other truted language, thats what the trusted part is about - not giving access to the rest of the system. I believe Peter E wrote something called pl/sh which gives shell access (search the archives for an address), or if that doesn't fit you're bill do a little C (PerlU + other untrusted languages can probably do this as well, but not tried). To make a C function easier, write the trigger function in plpgsql, and then have a mailer function to accept the necessary arguments then do a system command.

hth,
- Stuart