Running a system command with parameter inside a Function in Postgresql

Asked

Viewed 252 times

1

I’m having a hard time create a function in the Postgres execute a command shell linux, with one detail: it is a function in a Trigger after insert and I need to use some columns from NEW.

In Mysql, using the plugin "Mysql UDF" was very simple, the trigger was 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 on the PostgreSQL, I installed the language PL/sh, which enables the execution of any script sh, then 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 gets to run the file php, the problem is that this language does not recognize the nomenclature NEW, there the values in args that I receive are exactly the writings that I pass in the parameter.

Does anyone know any way to use the NEW in PL/sh?

Another solution would be to manually pass the three values I need per argument in creating the Trigger and in the function I would use $1, $2 and $3.
That would be possible somehow?

database postgresql function trigger

1 answer

0


Response taken from here: Original post.

You can access some values by 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 be more or less 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 useUS $1 $2 $3, because the extent plsh dump ALL columns into arguments so that they are declared in your table.

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

And will be less than $3 in plsh, assuming this is the third column on the board.

As a note, "Trigger’s" metadata are available through env vars.

Browser other questions tagged database postgresql function trigger

You are not signed in. Login or sign up in order to post.