Executing system command with argument in PostgreSQL function
I'm not sure if I was specific in the question, but I'm having a hard time creating a Postgres function that runs a Linux shell command, with one detail: it's a function in a trigger after insertion and I need to use some columns NEW
.
Whereas in MySQL using the "MySQL UDF" plugin it was pretty simple, the 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 PL / sh, which allows any shell script to run, 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 is executing the .php file correctly, the problem is that the language does not recognize the variables NEW
that I provide as arguments to PHP, so in args[]
I got "NEW.uniqueid"
, "NEW.linkedid"
and "NEW.eventtype"
.
So, does anyone know how to use an argument correctly NEW
in PL / sh? Another possible solution might be to manually set the three values that I need using arguments when splitting the trigger, but not allowed NEW
in arguments.
source to share
You can access some of the values in plsh triggers.
- UPDATE only offers OLD
- INSERT only offers NEW (duh)
- DELETE I have not tested
So you get these values using arguments like $ 1, $ 2
You will look 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 didn't use $1 $2 $3
it because it plsh
adds ALL dumps to the arguments so that they are declared in your table. So you can do something like INSERT INTO table1 (column3) VALUES (6);
and it will be under $3
in plsh
if it is the third column in the table.
As a side note, trigger metadata is available via env vars.
source to share
As far as I know, you can not access the motorcade NEW
and OLD
in PL / sh.
I would use PL / Perl or PL / Python for this purpose.
Here's 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();
source to share