Postgres COPY TO PROGRAM with dynamic command

Question

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?

Answer 1

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);