Postgres Pro
Downloads
Home   >   mailing lists

Thread: ON INSERT => execute AWK/SH/EXE?

ON INSERT => execute AWK/SH/EXE?

From
"Bima Djaloeis"
Date:
Hi there,

I am new to PostgreSQL, is it possible to create something so that

1) If I insert / update / delete an item from my DB...
2) ... an awk / shell / external program is executed in my UNIX System?

If yes, how do I do this and if no, thanks for telling.

Thanks for reading, any help is appreciated.

Re: ON INSERT => execute AWK/SH/EXE?

From
"Rodrigo De León"
Date:
On 9/17/07, Bima Djaloeis <bima.djaloeis.uni@googlemail.com> wrote:
> Thanks for reading, any help is appreciated.

Triggers + Untrusted PL/Perl, see:
1) http://www.postgresql.org/docs/8.2/static/plperl-triggers.html
2) http://www.postgresql.org/docs/8.2/static/plperl-trusted.html

Re: ON INSERT => execute AWK/SH/EXE?

From
"Scott Marlowe"
Date:
On 9/17/07, Bima Djaloeis <bima.djaloeis.uni@googlemail.com> wrote:
> Hi there,
>
> I am new to PostgreSQL, is it possible to create something so that
>
> 1) If I insert / update / delete an item from my DB...
> 2) ... an awk / shell / external program is executed in my UNIX System?
>
> If yes, how do I do this and if no, thanks for telling.

Yes.  you have to use an untrusted pl language, like pl/perlu or
pl/tclu and you have to be a superuser to create user defined
functions in those languages.

Re: ON INSERT => execute AWK/SH/EXE?

From
"A. Kretschmer"
Date:
am  Mon, dem 17.09.2007, um 18:50:46 +0200 mailte Bima Djaloeis folgendes:
> Hi there,
>
> I am new to PostgreSQL, is it possible to create something so that
>
> 1) If I insert / update / delete an item from my DB...
> 2) ... an awk / shell / external program is executed in my UNIX System?
>
> If yes, how do I do this and if no, thanks for telling.

You can do this, you need a untrusted language like plperlU or plsh.
Then you can write a TRIGGER and call external programs.


Hope that helps, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: ON INSERT => execute AWK/SH/EXE?

From
Erik Jones
Date:
On Sep 17, 2007, at 11:50 AM, Bima Djaloeis wrote:

> Hi there,
>
> I am new to PostgreSQL, is it possible to create something so that
>
> 1) If I insert / update / delete an item from my DB...
> 2) ... an awk / shell / external program is executed in my UNIX
> System?
>
> If yes, how do I do this and if no, thanks for telling.
>
> Thanks for reading, any help is appreciated.

You could use a trigger function in an untrusted procedural language
such as plperlu or plpythonu to do that.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Re: ON INSERT => execute AWK/SH/EXE?

From
Steve Atkins
Date:
On Sep 17, 2007, at 9:50 AM, Bima Djaloeis wrote:

> Hi there,
>
> I am new to PostgreSQL, is it possible to create something so that
>
> 1) If I insert / update / delete an item from my DB...
> 2) ... an awk / shell / external program is executed in my UNIX
> System?
>
> If yes, how do I do this and if no, thanks for telling.
>

Yes it's possible, but it's probably a really bad idea, so I'm not
going to tell you how.

Instead, use a trigger to store a message in a queue table, then have
an external persistent process poll the queue table (or use listen/
notify to sleep until new messages to be added to the queue).

Cheers,
   Steve

Re: ON INSERT => execute AWK/SH/EXE?

From
Richard Broersma Jr
Date:
--- "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote:

> You can do this, you need a untrusted language like plperlU or plsh.
> Then you can write a TRIGGER and call external programs.

This may be a silly question, will plsh work on a windows server?  I am pretty sure that plbat
doesn't exist :-).

Regards,
Richard Broersma Jr.

Re: ON INSERT => execute AWK/SH/EXE?

From
Chris Browne
Date:
bima.djaloeis.uni@googlemail.com ("Bima Djaloeis") writes:
> Hi there,
> I am new to PostgreSQL, is it possible to create something so that
> 1) If I insert / update / delete an item from my DB...
> 2) ... an awk / shell / external program is executed in my UNIX System?
> If yes, how do I do this and if no, thanks for telling.
> Thanks for reading, any help is appreciated.

I Would Not try to do that directly, as that could lead to arbitrary
numbers of processes getting scheduled, which could cause Plenty O
Heartburn.

I would instead suggest having a trigger in place that would, upon
doing this:

 a) Insert an ID, if needed, into a work queue table.
    (This may be optional.)

 b) Use NOTIFY to tell a process that uses LISTEN to wake up and
    do whatever work is necessary, possibly processing *multiple*
    items.

The LISTENING process needs to be prepared to process all the
queued-up work; that should lead to *vastly* more efficient processing
than spawning a worker for each item.
--
"cbbrowne","@","acm.org"
http://www3.sympatico.ca/cbbrowne/rdbms.html
Rules of the  Evil Overlord #60. "My five-year-old  child advisor will
also  be asked to  decipher any  code I  am thinking  of using.  If he
breaks the code  in under 30 seconds, it will not  be used. Note: this
also applies to passwords." <http://www.eviloverlord.com/>