im 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?&page='||(10::text)||"'; COPY _temp FROM PROGRAM _command;

i get

ERROR: syntax error at or near "_command"

this doesnt work either, you cant even concatenate in place

COPY _temp FROM PROGRAM 'curl -uri "https://url?&page='||(10::text)||'"';

percent parametric replacement like raise notice style doesnt work either

what gives? program is a literal string with single quotes ‘ ‘ so whats the difference between specifying a literal string and using a text or varchar variable? there doesnt 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?