Thread: running \copy through perl dbi ?

On 12/8/23 07:45, David Gauthier wrote:
> Hi:
> 
> 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...
> -c "\copy ...".  But I was wondering if there is a more elegant way to 
> do this, maybe some sort of legal SQL command that Perl/DBI would 
> swallow ???

Per David Johnston post:

https://metacpan.org/pod/DBD::Pg#COPY-support

> 
> Thanks in Advance !
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier <dfgpostgres@gmail.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 "\"

Hi David,

This works for me :

    #create file
    my $sql = qq {\\copy ( $sub_query ) to '$location' with  null as '' delimiter ';' csv header } ;

    my $db_name = 'xxxxxx' ;

    my @args = ( 'psql', '-c', $sql, $db_name ) ;

    system( @args ) == 0 or die "Bad copy: $?" ;


--

                    Bien à vous, Vincent Veyron

https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres d'assurance

On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier <dfgpostgres@gmail.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.

However, you need then to deal with permissions so that the server may write the file, so I wonder what approach is
cleaner?


--

                    Bien à vous, Vincent Veyron

https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres d'assurance

On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier <dfgpostgres@gmail.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.

However, you need then to deal with permissions so that the server may write the file, so I wonder what approach is the
mostelegant? 


--

                    Bien à vous, Vincent Veyron

https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres d'assurance

--
vv.lists <vv.lists@wanadoo.fr>

On 12/10/23 10:34, Johnson, Bruce E - (bjohnson) wrote:
> 
>> On Dec 10, 2023, at 10:41 AM, Vincent Veyron <vv.lists@wanadoo.fr 
>> <mailto:vv.lists@wanadoo.fr>> wrote:
>>
>> External Email
>>
>> On Fri, 8 Dec 2023 10:45:28 -0500

>>
>> 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)

The issue is \copy is not a SQl statement, it is a psql(Postgres command 
line client) meta-command. This means it can only be used in psql.

> -- 
> Bruce Johnson
> University of Arizona
> College of Pharmacy
> Information Technology Group
> 
> Institutions do not have opinions, merely customs
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com