Re: Record Separator with psql -c when output to a variable not a file! - Mailing list pgsql-general

From Martijn van Oosterhout
Subject Re: Record Separator with psql -c when output to a variable not a file!
Date
Msg-id 20101004212307.GA17209@svana.org
Whole thread Raw
In response to Record Separator with psql -c when output to a variable not a file!  (andrew harvey <a.andrewharvey@gmail.com>)
List pgsql-general
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

Attachment

pgsql-general by date:

Previous
From: Richard Huxton
Date:
Subject: Re: Record Separator with psql -c when output to a variable not a file!
Next
From: Thom Brown
Date:
Subject: Re: PG website testing