Re: How to execute a system command inside a SQL statement

Richard Broersma Jr wrote:
>> I want to execute 'java MyApp' everytime one row is inserted on a table.
>> What is the correct function to do it?
>>
>> I want something like this:
>>
>> CREATE TRIGGER executeJava
>>      AFTER INSERT ON mytable
>>      FOR EACH ROW
>>      BEGIN
>>      SYSTEM('java MyApp');
>>      END;
>>
>> I suppose SYSTEM('java MyApp') is not correct syntax. What is the correct
>> syntax to get it? Sorry, I'm relatively new on PostgreSQL.
>>
>> Anyone can help me, please?
>>
>
> I am not sure sure how you could go about doing this.  If you do not get the answer you are
> looking for on this list you might try the pg_general mailing list.
>
> Here is the only thing that I can think of that might work for you:
> http://pgfoundry.org/projects/pljava/
>
Just to add a general comment:

To do anything that interacts with the system in this way will require
an untrusted language.  There are untrusted versions of several
languages, but plpgsql does not have untrusted version.  See here:

http://www.postgresql.org/docs/8.1/interactive/xplang.html

The point of doing this is that the authentication procedure to the
database does not mirror that of the system.  Typically, one does not
want database users to have access to other system resources directly,
so there are limitations on what can be done with trusted languages like
plpgsql.  If you move over to using an untrusted language, these
restrictions are removed, allowing one to call system commands (and
other commands that interact with the system and/or files directly).

Sean