PostgreSQL › PostgreSQL - hackers

executing OS programs from pg

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

6 messages Options

Gevik babakhani

executing OS programs from pg

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.

Tom Lane-2

Re: 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

Bruno Wolff III

Re: executing OS programs from pg

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]

John Hansen

Re: executing OS programs from pg

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

"Hans-Jürgen Schönig (PostgreSQL)"

Re: executing OS programs from pg

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

Christopher Kings-Lynne

Re: executing OS programs from pg

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