Record Separator with psql -c when output to a variable not a file!

From: andrew harvey <a(dot)andrewharvey(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Record Separator with psql -c when output to a variable not a file!
Date: 2010-10-04 19:51:11
Message-ID: 7a4987b8-f323-452e-a6dd-236affe2d6ca@x7g2000yqg.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The default psql -c record separator is a newline when output is the
screen, console or a file. But what if I'm required to send output to
a variable and not to a file (or standard output?)

command="`psql -c "SELECT * FROM pg_stat_database`"

when you retain the query result within the variable $command you need
to count all the fields in the entire output in order to select the
particular one that you want. (because the record separator newline
doesn't apply to within the variable named above) Therefore all the
sql output is bundled up into one string with very, very many fields.

It so happened that field 38 was the number of pages served from disk
for one of my databases and field 53 turned out to be the number of
pages served from cache for another one of my databases. But this is
hardly a sensible way of producing results!

Is there a more sensible way of formatting the sql output within the
specified variable I've called $command?

I know that, obviously, if you output the result of the sql query to a
file and then use grep and awk you could have wonderful output all the
time. But there is a specific requirement here to do all the
formatting from within a variable!

Thanks,

Andrew

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wang, Mary Y 2010-10-04 19:59:51 How to data dump a table content to a CSV or XML format?
Previous Message Eric Comeau 2010-10-04 19:08:23 Re: Any advice on debugging hanging postgresql-8.1.21 (many postmaster's)