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?

Patrick · Accepted Answer · 2017-03-07 00:30:30Z

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

edited Mar 7, 2017 at 0:30

answered Mar 6, 2017 at 13:17

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

2 Comments

Fathur Rohman Over a year ago

Unfortunately this not work, it is produce error 42601] ERROR: syntax error at or near "FORMAT"

Patrick Over a year ago

@FathurRohman In that case the command expects a string literal. You could try wrapping the entire statement in an EXECUTE command. See updated answer.

Collectives™ on Stack Overflow

Postgres COPY TO PROGRAM with dynamic command

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related