Обсуждение: Record Separator with psql -c when output to a variable not a file!

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

On 04/10/10 20:51, andrew harvey wrote:
> 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`"

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

This probably won't work:
   echo $command | cut -f 2,4,5
This probably will:
   echo "$command" | cut -f 2,4,5

I hate shell - I always spend hours trying to get quoting to work properly.

Oh, for the archives Andrew is probably using a command like:
   psql -t --no-align --field-separator=$'\011'
That turns the header and footer off (tuples only) and sets the output
to unaligned tab-separated columns.

--
   Richard Huxton
   Archonet Ltd

On Mon, Oct 04, 2010 at 12:51:11PM -0700, andrew harvey wrote:
> 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.

Looks at the options to psql, for example you have the -A and -t
options which (IIRC) suppress the output of the header and the
extraneous spacing. You can control the delimiter also.

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

If you want a particular column, select only that column instead of
"SELECT *".

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
>                                       - Charles de Gaulle

On Mon, Oct 4, 2010 at 3:51 PM, andrew harvey <a.andrewharvey@gmail.com> wrote:
> 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.
>

To be clear, the backtick expression in the shell is what is eating
the newlines and converting them to spaces.  That's part of the
definition of how the backticks work.