Running system command with argument in a PostgreSQL function

Question

I'm not sure if I was specific in the question, but I'm having trouble creating a Postgres function that runs a Linux shell command, with one detail: it's a function in a Trigger after insert and I need to use some NEW columns.

While in MySQL, using the plugin "MySQL UDF" it was pretty simple, trigger worked 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

But on PostgreSQL I tried the language PL/sh, which enables running any shell script, so I wrote 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 does execute the .php file in proper way, the problem is the language does not recognize the NEW variables I'm giving as arguments to the PHP, so in the args[] what I got is "NEW.uniqueid", "NEW.linkedid" and "NEW.eventtype".

So, anyone knows how can I properly use the NEW argument in PL/sh? Another possible solution might be to manually set the three values I need via the arguments on crating the trigger, but it's not allowed to use NEW in the arguments.

an you please help with my similar question: stackoverflow.com/questions/61071706/… — ankit
– ankit, Commented Apr 7, 2020 at 5:19

Łukasz Kamiński · Accepted Answer · 2017-04-04 12:21:28Z

4

You can access some values in plsh triggers.

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

So you get those values using arguments, like $1, $2

You function would look kinda like this:

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

$$;

Notice that I didn't use $1 $2 $3, that is because plsh extension dumps ALL columns into arguments in order they are declared in your table. So you might do something like INSERT INTO table1 (column3) VALUES (6); and it will be under $3 in plsh, assuming this is third column in table.

As a side note, metadata of trigger is available thru env vars.

answered Apr 4, 2017 at 12:21

Łukasz Kamiński

6,0301 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pozs Over a year ago

DELETE should also report the OLD record as parameters. (because for all trigger events, it uses the tg_trigtuple property). -- Another side note that if the trigger definition has fixed parameters too, they will come before the column values (these seem to be extremely undocumented).

ankit Over a year ago

@Lukasz I have a command that return ankit\nankit1 as value(\n is new line) but i want to use these value to insert into other table. Can you please help me with this?

Laurenz Albe · Accepted Answer · 2017-04-04 13:31:56Z

2

As far as I know, you cannot access the NEWand OLD tuple in PL/sh.

I would use PL/Perl or PL/Python for this purpose.

Here is an example in PL/Python:

CREATE OR REPLACE FUNCTION pytrig() RETURNS trigger
   LANGUAGE plpythonu AS
$$import os
os.system("/usr/bin/php /home/laurenz/hello.php '" + TD["new"]["val"] + "'")$$;

CREATE TABLE test (id integer PRIMARY KEY, val text);

CREATE TRIGGER pytrig AFTER INSERT ON test FOR EACH ROW
   EXECUTE PROCEDURE pytrig();

edited Apr 4, 2017 at 13:31

answered Apr 4, 2017 at 12:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

3 Comments

Guilherme B Over a year ago

I did try using PL/Python, but had no success on executing "subprocess.call", would you have an example of running the php file?

Laurenz Albe Over a year ago

I have added a simple example. I am no Python expert though.

Guilherme B Over a year ago

Thank you @laurenz-albe, your solution is also correct for my question.

Collectives™ on Stack Overflow

Running system command with argument in a PostgreSQL function

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related