Executing a system command with a parameter inside a Function in PostgreSQL

Question

Executing a system command with a parameter inside a Function in PostgreSQL

Navigation

#1 by (0 votes)

1

I'm having a hard time creating a function in Postgres running a shell Linux command, with one detail: function Trigger after insert and I need to use some columns of NEW .

In Mysql , using plugin "MySQL UDF" was very simple, trigger looked like this:

BEGIN
   DECLARE result int(10);
   SET result = sys_exec
         (
             '/usr/bin/php /var/www/html/.../regras.php 
              NEW.uniqueid NEW.linkedid NEW.eventtype'
         );
END

Now in PostgreSQL , I installed language PL/sh , which makes it possible to execute any script sh , so I tried the following function :

   CREATE FUNCTION tarifador_func2() RETURNS TRIGGER
     LANGUAGE plsh
   AS $$
     #!/bin/sh
     /usr/bin/php /var/www/html/...regras.php 
     NEW.uniqueid NEW.linkedid NEW.eventtype
   $$;

It even runs the php file, the problem is that this language does not recognize the NEW nomenclature, so the values in the args that I get are exactly the ones I pass in the parameter. >

Does anyone know how to use NEW in PL/sh ?

Another solution would be to manually pass the three values I need per argument in the creation of Trigger and in function I would use $1 , $2 and $3 . Is this possible in any way?

postgresql trigger

asked by anonymous 03.04.2017 / 22:58

1 answer

Table of distances google maps api and python AngularJS Services API. Do not load TITLE while sharing link

score 0 · Accepted Answer

Response taken from here: Original post .

You can access some values through plsh triggers.

UPDATE offers only OLD
INSERT offers only NEW (duh)
DELETE I didn't test

Then you get these values using arguments $1, $2

Your function would look something like this:

CREATE FUNCTION tarifador_func2() RETURNS TRIGGER
LANGUAGE plsh
AS $$
#!/bin/sh
/usr/bin/php /var/www/html/...regras.php $3 $6 $1

$$;

Note that I did not use US $1 $2 $3 , because the plsh extension drops ALL columns into arguments so they are declared in your table.

So you can do something like INSERT INTO table1 (column3) VALUES (6);

E will be less than $3 in plsh , assuming this to be the third column in the frame.

As a side note, the trigger's metadata is available through env vars .