How do I use a dynamic command with copy from program? I'm trying to read a JSON API with the Postgres copy from program command.

When I call 

copy _temp from program 'curl -uri "https://url"';


it works fine but the API has paging and I need to loop through multiple calls.

When I call it like this:

_command := 'curl -uri "https://url?&amp;page='||(10::text)||"';
copy _temp from program _command;`


I get

ERROR:  syntax error at or near "_command"


You can't even concatenate in place like

copy _temp from program 'curl -uri "https://url?&amp;page='||(10::text)||'"';


Percent parametric replacement in the raise notice style doesn't work either.

What gives? program is a literal string with single quotes so what is the difference between specifying a literal string and using a text or varchar variable? There doesn't seem to be any program data type (::program cast does nothing), what am I missing? 

In the docs it says 'it is best to use a fixed command string' not that you can only use a fixed command string...

How do I use a dynamic command string?
If you want to use dynamic commands, you need the right context for it.  Plain SQL is not one.

One possibility is using PL/pgSQL, and it's EXECUTE functionality.  Use DO to get into the context:

DO $$
DECLARE page integer;
BEGIN
FOR page IN SELECT i FROM generate_series(1, 10) AS t(i)
LOOP
    EXECUTE format($cmd$ COPY _temp 
                         FROM PROGRAM 'curl -uri "https://url?&amp;page=%s"'$cmd$,
                   page::text);
END LOOP;
END; $$;


The other way is to run the loop on the shell side.  The following example is using bash:

for i in $(seq 1 10); do
    psql -c "COPY _temp FROM PROGRAM 'curl -uri https://url?&amp;page=${i}'"
done


Notes:


I am using two levels of dollar-quoting ($$ and $cmd$) to prevent quote multiplication.
I am also using format() instead of the messy concatenation.
If you really have to quote the URI of the curl command, you will have to escape the quotes in there.