I'm trying to run a PG client side "\copy" command from a perl script. I tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'... ERROR: syntax error at or near "\"
I can do this with a command line approach, attaching to the DB then run using...
Duh! I just realized that what I proposed with system() is a command line approach.
As David Johnston mentionned, you can use the SQL COPY command.
One thing to remember with the Perl DBI is that you can use a string variable in the $dbh->do() command.
Perl uses 2 different string variable delimiters:
1) ‘ ‘ , which is exactly what you enter $s= ‘\copy * from foo as json’; will send that to the database without the need for escaping anything (unless you need to enter an actual ‘ in the command, in which case method two is better)
2) “ “ , which allows for declared perl variables to be substituted in the string:$table=‘foo’;$type=‘json’;$cmd=‘\copy’;$s= “$cmd * from $table as $type”;
Concatenation (periods between strings) works as well: $s = ‘\copy ‘.”* from foo as json”;
Then $dbh->do($s); will work in alll three cases.
Been using perl and DBI for (does quick math, ulp!) over 20 years now wrangling a lot of things like this.
-- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group