Re: [EXT]Re: running \copy through perl dbi ?

On Dec 10, 2023, at 10:41 AM, Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr<mailto:vv(dot)lists(at)wanadoo(dot)fr>> wrote:

External Email

On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier <dfgpostgres(at)gmail(dot)com<mailto:dfgpostgres(at)gmail(dot)com>> wrote:

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

Institutions do not have opinions, merely customs