Question
How to load data to PostgreSQL managed database via public api in a loop
I want to load data via public api. Data comes in chunks, so the loop is needed to do the job. The simplest option is writing the script like
DO $$
BEGIN
FOR counter IN 1..5 LOOP
copy weather_json FROM PROGRAM 'curl -X GET "http://api.spending.gov.ua/api/v2/dictionaries/contractors" -H "accept: application/json"';
END LOOP;
END; $$
however, the ‘copy’ command requires the superuser privileges, which is not an option in case of a managed database. on the other hand, we have a psql ’\copy’ command, which solves the problem with privileges, but cannot be used in a loop.
can anyone suggest any viable solution or workaround?