Date: Mon, 29 Dec 2025 17:40:23 +0100
From: Vincent Veyron <vv.lists@wanadoo.fr>
To: depesz@depesz.com
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, pgsql-general
 <pgsql-general@postgresql.org>
Subject: Re: psql: print values and return the COUNT(*) value to bash?
Message-Id: <20251229174023.106f1c1f2212c7372615673c@wanadoo.fr>
In-Reply-To: <aVJT7n_f7kEjO0Qb@depesz.com>
References: <CANzqJaBX-SZZcHn71xgqtSKx6ekZGdHPSgCmP2co5=-KDu4Q-Q@mail.gmail.com>
	<aVJT7n_f7kEjO0Qb@depesz.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/20251229174023.106f1c1f2212c7372615673c%40wanadoo.fr>
Precedence: bulk

On Mon, 29 Dec 2025 11:11:58 +0100
hubert depesz lubaczewski <depesz@depesz.com> wrote:

Hi Ron, Hubert,

>=20
> Example:
>=20
> =3D$ cat z.sh
> #!/usr/bin/env bash
>=20
> table=3Dpg_stat_all_tables
> field=3Dlast_autovacuum
>=20
> returned=3D"$( psql -d depesz_explain -v "t=3D$table" -v "f=3D$field" -X =
<< _SQL_
> select current_timestamp - min(:"f") as days_ago
>           , min(:"f") as oldest_date
>           , count(*) as cnt
>      from :"t"
> _SQL_
> )"
> row_count=3D"$( tail -n1 <<< "${returned}" | tr -cd 0-9 )"
> echo "Rowcount =3D $row_count"
> echo "Full output:"
> echo "${returned}"
>=20
> # vim: set filetype=3Dbash shiftwidth=3D4 expandtab smarttab softtabstop=
=3D4 tabstop=3D4 textwidth=3D132 :
>=20
> =3D$ bash z.sh
> Rowcount =3D 1
> Full output:
>         days_ago        |          oldest_date          | cnt
> ------------------------+-------------------------------+-----
>  7 days 03:48:07.348247 | 2025-12-22 07:22:32.593814+01 | 212
> (1 row)
>=20

IIUC, Ron wants the 212 number?

Maybe use the -t switch, and 'cut' :

#!/usr/bin/env bash

table=3Dpg_stat_all_tables
field=3Dlast_autovacuum

returned=3D"$( psql -t -d vv -v "t=3D$table" -v "f=3D$field" -X << _SQL_
select current_timestamp - min(:"f") as days_ago
          , min(:"f") as oldest_date
          , count(*) as cnt
     from :"t"
_SQL_
)"
row_count=3D"$( tail -n1 <<< "${returned}" | cut -d "|" -f 3)"
echo "Rowcount =3D $row_count"
echo "Full output:"
echo "${returned}"


--=20
				Bien =E0 vous, Vincent Veyron=20

https://compta.libremen.com
Logiciel libre de comptabilit=E9 g=E9n=E9rale et analytique en partie double