3

I have command that I want to execute after insert in table event, the command is:

/usr/bin/php /var/www/app/artisan broadcast --sid=14 --cid=89

Which number 14 and 89 are dynamic depending on inserted data.

For that purpose, I create trigger like this:

CREATE TRIGGER insert_event 
AFTER INSERT ON event 
FOR EACH ROW 
EXECUTE PROCEDURE call_artisan_broadcast();

And I create trigger function like this:

CREATE OR REPLACE FUNCTION call_artisan_broadcast()
RETURNS TRIGGER AS
$BODY$
  BEGIN

    COPY (SELECT 1) TO PROGRAM '/usr/bin/php /var/www/app/artisan broadcast --sid='||NEW.sid||' --cid='||NEW.cid;
    RETURN NEW;
  END
$BODY$

LANGUAGE plpgsql VOLATILE;

That trigger produce error

[42601] ERROR: syntax error at or near "||"

This trigger work fine if the command not have concatenation. I do not know how to concatenate command string with variable NEW in COPY TO PROGRAM.

My question is how to concatenate string in COPY TO PROGRAM in trigger?

Fathur Rohman
  • 73
  • 3
  • 6

1 Answers1

4

Try this:

EXECUTE format('COPY (SELECT 1) TO PROGRAM ''/usr/bin/php /var/www/app/artisan broadcast --sid=%s --cid=%s'' ', NEW.sid, NEW.cid);
Patrick
  • 29,357
  • 6
  • 62
  • 90
  • Unfortunately this not work, it is produce error `42601] ERROR: syntax error at or near "FORMAT"` – Fathur Rohman Mar 06 '17 at 13:31
  • 1
    @FathurRohman In that case the command expects a string literal. You could try wrapping the entire statement in an `EXECUTE` command. See updated answer. – Patrick Mar 06 '17 at 13:44