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).
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
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, Oct 6, 2015 at 10:04 AM, John McKown <john.archie.mckown@gmail.com>
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. 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).--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Tue, Oct 6, 2015 at 10:04 AM, John McKown <john.archie.mckown@gmail.com>
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. 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)
While not an expert on awk I believe this ^ is how the program is designed
to function.
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.
Birt, Jasper Reports, or Crystal Reports to name a few well-known ones.
Yeah, sometimes I'm just plain stupid & pig headed. But then it will be a
"leaning experience" (with associated scars, I'm sure).
If you don't understand the nature of awk or *NIX in general (dll is a
Window's thing) you are likely going to end up with much worse than scars.
Amputation is likely to be required.
To a large degree one is expected to use BASH (or another shell scripting
language) if they want to compose programs from *NIX commands. To choose
to use awk but then discard psql makes little sense. If you need something
more holistic and/or capable you should just upgrade to a general-purpose
language (e.g., Perl) or a custom-built solution (e.g. Birt).
David J.
On Tue, Oct 6, 2015 at 10:15 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:
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
I presume you mean "awk <blah> your_output_file" ...
David J.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Oct 6, 2015 at 9:25 AM, Reid Thompson <Reid.Thompson@ateb.com>
wrote:
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
Thanks. I missed that. The GAWK documentation is a bit difficult to get
through. I depended way too much on a Google search.
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
--
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
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
On Oct 6, 2015, at 9:25 AM, Reid Thompson <Reid.Thompson@ateb.com> wrote:
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Oct 6, 2015 at 10:38 AM, Basques, Bob (CI-StPaul) <
bob.basques@ci.stpaul.mn.us> wrote:
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.
Me too. I'm in an "learn awk" mode. I already have a couple of PERL
programs which use DBI to load tabular information into a series of
PostgreSQL tables. The information is actually meant to be loaded into
IBM's DB/2. I have a PERL program which can read the DB/2 load utility's
control file and create a PERL program which can read the data file read
using that control file. The created PERL program sends the information
into the appropriate PostgreSQL tables instead. IMO, a rather nifty way to
have a PERL program write another PERL program for me.
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
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown