Perl dbi running psql code

Question

I was wondering if anyone had any experience with perl dbi connecting to a postgres database. So I can connect to my postgres database and execute select queries, but I can't execute psql queries. Does anyone know if it is possible to do this in perl dbi? Right now I use this code to fetch a select query:

my $SQL = $dbh->prepare("select*from pg_settings;);
    $SQL -> execute();
    while ( my ($settings_fetch) = $SQL->fetchrow_array() )
    {
        return $settings_fetch;
    }

with the module PG:DBI

But when I try to put \x or \pset format wrapped in the prepare statement it does not work. It doesn't give any errors but the output won't wrap and i still get the same output. Now I am a little confused if it's because it does not execute the psql commands and wrap the fetched rows, or the selects run always the same way and the wrapping is only to show on the display and can't be fetched with dbi

The reason I want this is because I fetch the select arrays inside an array and output this to word, but when I got big select output with a lot of columns it does get messy in word tables, so I wanted the \x variant inside my word document so that I don't get 10 columns horizontally but vertically.

That Perl code won't compile. Please post what you really have. — Borodin

Quentin Quentin · Accepted Answer · 2018-05-07T12:27:47

\x and \pset are formatting commands used by the psql client. They are not used by the Postgres server.

If you want to format the data output from your Perl program, you have to do the formatting in Perl.

For example, rather than writing:

foreach my $column (@$settings_fetch) {
    print $column;
}

You could:

foreach my $column (@$settings_fetch) {
    print $column . "\n";
}

Perl dbi running psql code

1 Answers