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?&page='||(10::text)||"'; copy _temp from program _command;`
ERROR: syntax error at or near "_command"
You can't even concatenate in place like
copy _temp from program 'curl -uri "https://url?&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?