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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-----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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 which listens
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Sep 29, 2016 at 2:41 PM, Armand Pirvu (home) <armand.pirvu@gmail.com
wrote:
I used plsh extension but I had to use two functions and a trigger, see
code below
I don't see any way to not use a trigger given your requirement. And as
written I don't see that you need the second function - just write the
trigger function in plsh. Admittedly its probably better to have two
functions from an architecture standpoint.
Listen/Notify doesn't seem to make sense as you want the server to ensure
that the file exists - not some other client that may or may not be
connected.
David J.
Thanks for replies.
Good deal of info
While plsh seems to be an easier approach , I am also inclined in a an ESQL/C type app to listen to an event
@Igor Neyman
It is just maybe I have missed a sample of a C app in that area, similar like ESQLC
Cheers
Armand
On Sep 30, 2016, at 10:45 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Show quoted text
On Thu, Sep 29, 2016 at 2:41 PM, Armand Pirvu (home) <armand.pirvu@gmail.com> wrote:
I used plsh extension but I had to use two functions and a trigger, see code belowI don't see any way to not use a trigger given your requirement. And as written I don't see that you need the second function - just write the trigger function in plsh. Admittedly its probably better to have two functions from an architecture standpoint.
Listen/Notify doesn't seem to make sense as you want the server to ensure that the file exists - not some other client that may or may not be connected.
David J.