Trigger-Function arguments PLSH

Question

I created a shell script which take an integer argument to send me a mail.

Now I try to create a trigger on my psql database which send the id of the inserted row which fired the trigger to my shell script using the plsh extension.

So I created a function sendMailContact() and a trigger mailContact :

TRIGGER mailContact :

CREATE TRIGGER mailcontact
  AFTER INSERT
  ON contact
  FOR EACH ROW
  EXECUTE PROCEDURE sendmailcontact(NEW);

FUNCTION sendMailContact :

CREATE OR REPLACE FUNCTION sendmailcontact()
RETURNS trigger AS '
#!/bin/sh
/home/me/Scripts/sendMailContact.sh NEW.id'
LANGUAGE plsh;

But the "NEW.id" seems to doesn't works properly (Here it's part of the code because I was not able to pass it as a variable). According to this answer : https://stackoverflow.com/a/35654749/5591761 And from the official README file, the NEW.x has to work but here it doesn't.

I also tried the TG_ARGV[0] which is used to pass argument from trigger to function in standard SQL langage but I don't really know how to use it here. Maybe the plsh extension provide us a new table of argument but I didn't find it.

Finally I tried to pass directly the id to the function and use it with $1 on my function but it doesn't work aswell

How can I pass the id (integer) of the inserted row to my function and use it as an argument for my shell script ?

----- EDIT : -----

After I took a look at the answers you provided me, I edited my function like this :

CREATE OR REPLACE FUNCTION sendmailcontact()
RETURNS trigger AS
$BODY$
#!/bin/sh
/home/pi/Scripts/sendMailContact.sh $2
$BODY$
LANGUAGE plsh

I used $2 because the id is the first column of the INSERT (id is a serial auto incremental but I think it's a part of the INSERT aswell ?)

Then the trigger :

CREATE TRIGGER mailcontact
  AFTER INSERT
  ON contact
  FOR EACH ROW
  EXECUTE PROCEDURE sendmailcontact(NEW);

It still don't working.

But I noticed that the INSERT doesn't work anymore if the trigger is activated. Nothing is added to my table.

In case you are asking if the script or the trigger work the answer is yes, before this I wrote the almost same trigger and function with the trigger calling the function when I INSERT in the contact. The function was just sending me a mail without any argument and either the mail and the INSERT were working.

Possible duplicate of [Running system command with argument in a PostgreSQL function](https://stackoverflow.com/questions/43205594/running-system-command-with-argument-in-a-postgresql-function) — pozs, Jun 09 '17 at 14:02
The linked answer should contain enough information, but: `EXECUTE PROCEDURE sendmailcontact(NEW)` **don't do that**. That will mean you want to use `'NEW'` **as a string** as a constant trigger parameter & plsh will prepend that to its parameters (so `$1` will be the string `NEW`, `$2` is the first column, etc.) — pozs, Jun 09 '17 at 14:05

score 0 · Answer 1 · answered Apr 25 '18 at 19:38

you can check the docs about triggers in plsh

example:

https://github.com/petere/plsh/blob/master/test/sql/trigger.sql

output:

https://github.com/petere/plsh/blob/master/test/expected/trigger.out

maybe you questions is about this code part inside trigger : ... for arg do echo "Arg is '$arg'" done ...

score 0 · Answer 2 · answered Apr 24 '20 at 12:47

You don't need to pass any variable to the trigger function, just use bash $0..$n variables (the values are in the same order of the columns definition order).

Here a small complete demo:

CREATE TABLE demo_table(column1 varchar, column2 varchar);

CREATE FUNCTION demo_trigger_fn() RETURNS trigger AS $$
#!/bin/sh
# Prints on stderr for demo purposes
>&2 echo "column1=$1"
>&2 echo "column2=$2"
exit 0
$$ LANGUAGE plsh;

CREATE TRIGGER demo_trigger
    AFTER INSERT ON demo_table
    FOR EACH ROW
    EXECUTE PROCEDURE demo_trigger_fn();

INSERT INTO demo_table(column1, column2) VALUES('value1', 'value2');

Trigger-Function arguments PLSH

2 Answers2