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.
"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
Show quoted text
-----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?
Import Notes
Resolved by subject fallback
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:
Show quoted text
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.