Horton
Menu

Thread

  1. running \copy through perl dbi ?

    David Gauthier <dfgpostgres@gmail.com> — 2023-12-08T15:45:28Z

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

Thanks in Advance !

  2. Re: running \copy through perl dbi ?

    David G. Johnston <david.g.johnston@gmail.com> — 2023-12-08T15:56:47Z

    On Fri, Dec 8, 2023 at 8:45 AM 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 "\"
>

More accurately it is the psql application's meta-command.  There is no
such thing as a generic client side command.

>
> 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 ???
>
>
The SQL command is just "COPY".  Search for how your driver exposes working
with that command/protocol.

David J.

  3. Re: running \copy through perl dbi ?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2023-12-08T16:01:37Z

    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

  4. Re: running \copy through perl dbi ?

    David G. Johnston <david.g.johnston@gmail.com> — 2023-12-08T16:05:49Z

    On Fri, Dec 8, 2023 at 9:01 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> 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
>
>
Right.  Since the copy api isn't really a standard thing in the SQL world
you cannot usually use generic database APIs, you need to use
driver-specific interfaces.

David J.

  5. Re: running \copy through perl dbi ?

    Vincent Veyron <vv.lists@wanadoo.fr> — 2023-12-08T18:12:59Z

    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

  6. Re: running \copy through perl dbi ?

    Vincent Veyron <vincent.veyron@libremen.org> — 2023-12-10T17:39:24Z

    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

  7. Re: running \copy through perl dbi ?

    Vincent Veyron <vv.lists@wanadoo.fr> — 2023-12-10T17:41:32Z

    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 most elegant?


-- 

					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>

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

    Johnson, Bruce E - (bjohnson) <bjohnson@arizona.edu> — 2023-12-10T18:34:55Z

    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
David Gauthier <dfgpostgres@gmail.com<mailto: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.

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

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2023-12-10T20:02:52Z

    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

  10. Re: running \copy through perl dbi ?

    Chris Travers <chris.travers@gmail.com> — 2023-12-11T10:00:17Z

    \copy in psql just wraps PostgreSQL's COPY FROM STDIN.

if you are trying to do it from your own client program it is trivial to
change to that call instead.

On Mon, Dec 11, 2023 at 4:09 PM Vincent Veyron <vincent.veyron@libremen.org>
wrote:

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

I wouldn't do COPY FROM FILE in that case.  I would do COPY FROM STDIN and
hten write the data.

Here's the general docs in the DBD::Pg module:
https://metacpan.org/pod/DBD::Pg#COPY-support

The general approach is to COPY FROM STDIN and then use pg_putcopydata for
each row, and finally pg_putcopyend to close out this.  It's not too
different from what psql does in the background.

>
>
> --
>
>                                         Bien à vous, Vincent Veyron
>
> https://marica.fr
> Logiciel de gestion des contentieux juridiques, des contrats et des
> sinistres d'assurance
>
>
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more