How to load data to PostgreSQL managed database via public api in a loop

Posted May 25, 2020 929 views
PostgreSQLDigitalOcean Managed PostgreSQL Database

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 $$
   FOR counter IN 1..5 LOOP
    copy weather_json FROM PROGRAM 'curl -X GET "" -H "accept: application/json"';
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?

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!