Thread: executing os commands from a function

All

I know this may sound like heresy since it involves executing an OS command from a function , but here goes

After an insert in a table, I want to touch a file

I.e

After insert into table test values (100) I want in a dir to have file 100

I used plsh extension but I had to use two functions and a trigger, see code below

CREATE or REPLACE FUNCTION func2 (var1 text) RETURNS text AS '
#!/bin/bash
 touch /home/postgres/$1;
' LANGUAGE plsh;
commit;

CREATE FUNCTION func1() RETURNS trigger AS '
BEGIN
perform   func2(NEW.col1);
RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER trigf1 BEFORE INSERT on test
    FOR EACH ROW EXECUTE PROCEDURE func1();

testdb=# insert into test3 values (777);
INSERT 0 1
testdb=# select * from test3;
 col1
------
  777

[postgres@edb1 ~]$ ls -ltr
-rw------- 1 postgres postgres     0 Sep 29 16:30 777


It works but can I be simpler ? Any other alternatives ? In Ingres for example I can use dbevent and an esqlc app which
listens 


Thank you

Armand

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Armand Pirvu (home)
Sent: Thursday, September 29, 2016 5:42 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] executing os commands from a function


All

I know this may sound like heresy since it involves executing an OS command from a function , but here goes

After an insert in a table, I want to touch a file

I.e

After insert into table test values (100) I want in a dir to have file 100

I used plsh extension but I had to use two functions and a trigger, see code below

CREATE or REPLACE FUNCTION func2 (var1 text) RETURNS text AS '
#!/bin/bash
 touch /home/postgres/$1;
' LANGUAGE plsh;
commit;

CREATE FUNCTION func1() RETURNS trigger AS '
BEGIN
perform   func2(NEW.col1);
RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER trigf1 BEFORE INSERT on test
    FOR EACH ROW EXECUTE PROCEDURE func1();

testdb=# insert into test3 values (777); INSERT 0 1 testdb=# select * from test3;
 col1
------
  777

[postgres@edb1 ~]$ ls -ltr
-rw------- 1 postgres postgres     0 Sep 29 16:30 777


It works but can I be simpler ? Any other alternatives ? In Ingres for example I can use dbevent and an esqlc app which
listens 


Thank you

Armand
__________________________________________________________________________________________________________

Similar mechanism exists in Postgresql.
Read about LISTEN/NOTIFY in the docs.

Regards,
Igor Neyman

Armand....

On Thu, Sep 29, 2016 at 11:41 PM, Armand Pirvu (home)
<armand.pirvu@gmail.com> wrote:
> I know this may sound like heresy since it involves executing an OS command from a function , but here goes
> After an insert in a table, I want to touch a file
....
> I used plsh extension but I had to use two functions and a trigger, see code below
....
> It works but can I be simpler ? Any other alternatives ? In Ingres for example I can use dbevent and an esqlc app
whichlistens 

If you are superuser ( which I supose you must be to execute plsh )
you could try to put code like this in a plpgsql, or may be even sql,
security definer ( or plain if not needed ) function:

( slighly sanitized )
In the psql prompt of a client machine:

n=# copy (select 1 as c where false) to '/tmp/ptxtst';
COPY 0

In the server machine:

postgres@server ~ $ stat /tmp/ptxtst
  File: '/tmp/ptxtst'
  Size: 0             Blocks: 0          IO Block: 4096   regular empty file
Device: 802h/2050d    Inode: 4721101     Links: 1
Access: (0644/-rw-r--r--)  Uid: (   70/postgres)   Gid: (   70/postgres)
Access: 2016-09-30 17:31:21.024617892 +0200
Modify: 2016-09-30 17:31:21.024617892 +0200
Change: 2016-09-30 17:31:21.024617892 +0200
 Birth: -

Further details left for the reader.

Francisco Olarte.