Thread: executing OS programs from pg

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Dear people,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Does anyone know how to execute an OS command from pgsql. I would like to create a trigger that op
onfiring would run/execute an external program.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Does such functionality exist or do I have to write my own trigger function in C.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Reagrds,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Gevik.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font></div>

"Gevik babakhani" <gevik@xs4all.nl> writes:
> Does anyone know how to execute an OS command from pgsql. I would like to
> create a trigger that op on firing would run/execute an external program.

Use any of the "untrusted" PLs to execute system() or the like.

Whether this is a good idea or not is a different question --- there are
excellent reasons why it is a *bad* idea to execute outside-the-database
actions from within a trigger.  Mainly that the actions won't be undone
if the transaction later rolls back, and now your database state is
inconsistent with outside-the-database state.  See the list archives for
many past discussions of this point and safer ways to design your
application.

(BTW, this is hardly material for -hackers.)
        regards, tom lane

On Fri, Jun 03, 2005 at 20:56:44 +0200, Gevik babakhani <gevik@xs4all.nl> wrote:
> Dear people,
> 
>  
> 
> Does anyone know how to execute an OS command from pgsql. I would like to
> create a trigger that op on firing would run/execute an external program.
> 
> Does such functionality exist or do I have to write my own trigger function
> in C.

You would have to write your own trigger, though you could use other languages
than C (e.g. untrusted perl).

Another option is to communicate with an external program using notify.

Look at peter eisentraut's procedural language PL/sh
It's on pgfoundry.

... John

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Tom Lane
> Sent: Saturday, June 04, 2005 5:16 AM
> To: Gevik babakhani
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] executing OS programs from pg
>
> "Gevik babakhani" <gevik@xs4all.nl> writes:
> > Does anyone know how to execute an OS command from pgsql. I
> would like
> > to create a trigger that op on firing would run/execute an
> external program.
>
> Use any of the "untrusted" PLs to execute system() or the like.
>
> Whether this is a good idea or not is a different question
> --- there are excellent reasons why it is a *bad* idea to
> execute outside-the-database actions from within a trigger.
> Mainly that the actions won't be undone if the transaction
> later rolls back, and now your database state is inconsistent
> with outside-the-database state.  See the list archives for
> many past discussions of this point and safer ways to design
> your application.
>
> (BTW, this is hardly material for -hackers.)
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>

Gevik babakhani wrote:
> Dear people,
> 
>  
> 
> Does anyone know how to execute an OS command from pgsql. I would like 
> to create a trigger that op on firing would run/execute an external program.
> 
> Does such functionality exist or do I have to write my own trigger 
> function in C.
> 
>  
> 
> Reagrds,
> 
> Gevik.
> 
>  
> 


Gevik,

Do something like that ...

CREATE OR REPLACE FUNCTION xclock() RETURNS int4 AS '        system("xclock");        return 1;
' LANGUAGE 'plperlu';

This should be fairly easy to implement but recall - you cannot rollback 
xclock ;).
best regards,
    hans


-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/664/393 39 74
www.cybertec.at, www.postgresql.at

Try the PL/sh project on www.pgfoundry.org.

Chris


Gevik babakhani wrote:
> 
> 
> Dear people,
> 
>  
> 
> Does anyone know how to execute an OS command from pgsql. I would like 
> to create a trigger that op on firing would run/execute an external 
> program.
> 
> Does such functionality exist or do I have to write my own trigger 
> function in C.
> 
>  
> 
> Reagrds,
> 
> Gevik.
> 
>  
>