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?

Subscribe
Share

Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!