executing OS programs from pg

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

executing OS programs from pg

Gevik babakhani

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.

 

Reply | Threaded
Open this post in threaded view
|

Re: executing OS programs from pg

Tom Lane-2
"Gevik babakhani" <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: executing OS programs from pg

Bruno Wolff III
In reply to this post by Gevik babakhani
On Fri, Jun 03, 2005 at 20:56:44 +0200,
  Gevik babakhani <[hidden email]> 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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: executing OS programs from pg

John Hansen
In reply to this post by Gevik babakhani
Look at peter eisentraut's procedural language PL/sh
It's on pgfoundry.

... John

> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Tom Lane
> Sent: Saturday, June 04, 2005 5:16 AM
> To: Gevik babakhani
> Cc: [hidden email]
> Subject: Re: [HACKERS] executing OS programs from pg
>
> "Gevik babakhani" <[hidden email]> 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
>
>

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: executing OS programs from pg

"Hans-Jürgen Schönig (PostgreSQL)"
In reply to this post by Gevik babakhani
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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: executing OS programs from pg

Christopher Kings-Lynne
In reply to this post by Gevik babakhani
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.
>
>  
>

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq