By quazerro
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?
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Heya,
Just came across this answer and decided to write some general guidelines for anyone who comes across this in the future despite the old question.
You’re on the right track. The psql ‘\copy’ command is definitely a solution for dealing with this issue since it bypasses the need for superuser privileges. However, as you mentioned, it doesn’t work well with loops.
A possible workaround is to use a shell loop with the psql -c call from the shell script. This way, you can iterate over your code and use the \copy command. Here’s a simple example:
- bash for counter in {1..5} do psql -c "\copy (SELECT * FROM my_table) To '/path/to/my/file.csv' With CSV" done
Note: This example expects that you have your database connection environment variables (like PGDATABASE, PGHOST, etc.) set properly.
Please remember that this is a basic example. Depending on the exact nature of your use case, you may need to adapt it accordingly.
For more detailed usage and information about PostgreSQL on DigitalOcean, please check out this link.
Hope that this helps!
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and SMBs
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.