Thread: Processing data from table using awk.

I'm wanting to do some reporting on data which I have an a PostgreSQL table. For lack of anything better, I've decided to see if I can do it in GNU awk. OK, using Perl with DBI might be a better idea, I'll grant you that. Or maybe Python or Ruby (which I don't know). But out of shear cussedness, I'm going to see what I can do in gawk. What I don't see is a way to get the data out of PostgreSQL and into my awk program. Does anybody know of a way to do this, short of "cheating" by using psql? Yes, I know that I could do something like (BASH on Linux/Fedora 22 x86_64):

awk -f some-program.awk <(echo 'SELECT a, b, c, d FROM schema.table ORDER BY a' | psql dbname) 

What I was hoping for was a "Dynamic Extension" (dll) which would allow "native" use of PostgreSQL, or may ODBC. But I haven't found any. Have I missed anything? If I were to create such a beastie, would it be of any use to others? I guess, in this, I'm wondering what "report writer" most are using when psql just doesn't have sufficient capability.

Yeah, sometimes I'm just plain stupid & pig headed. But then it will be a "leaning experience" (with associated scars, I'm sure).

Your best bet is something like

#!/bin/bash

get_data ()
{
QRY=$(psql $HOST $PORT $USER $DBNAME <<_QUERY_

\o your_output_file
SELECT col1, col2, ...., coln
  FROM your_table
WHERE <blah>;

_QUERY_

)
}

awk <blah> your_table

On Tue, 2015-10-06 at 09:04 -0500, John McKown wrote:

> I'm wanting to do some reporting on data which I have an a PostgreSQL table.
> For lack of anything better, I've decided to see if I can do it in GNU awk.


perhaps... note the 4th extension...

https://www.gnu.org/software/gawk/manual/html_node/gawkextlib.html
As of this writing, there are seven extensions:

    errno extension
    GD graphics library extension
    PDF extension
    PostgreSQL extension

    MPFR library extension (this provides access to a number of MPFR functions that gawk’s native MPFR support does not)
    Redis extension
    XML parser extension, using the Expat XML parsing librar
--

Just to throw in an idea.

I almost exclusively use PERL for this type of thing.  A bunch of examples out on the web using DBI, and the main aspects are portable across many databases, not just POSTGRES.

Just my two cents.

AWK would work too, I’ve used it myself, and got very complicated with it as well, but you’ll eventually end up looking for more capabilities, and start pulling in other commands like SED, etc.  Perl just keeps on working.

bobb