Skip site navigation (1) Skip section navigation (2)

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

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
Cc: developer_student <juamagb1(at)gmail(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: How to execute a system command inside a SQL statement
Date: 2006-11-24 02:03:40
Message-ID: 456652FC.5070508@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
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



In response to

pgsql-novice by date

Next:From: operationsengineer1Date: 2006-11-24 10:10:48
Subject: Data (Table) Structure Question
Previous:From: Richard Broersma JrDate: 2006-11-23 22:33:24
Subject: Re: How to execute a system command inside a SQL statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group