On Mon, Dec 29, 2025 at 02:21:41AM -0500, Ron Johnson wrote:
> Currently I do this in a bash script:
> Tbl=table_1
> Fld=field_1
> Sql="SELECT CURRENT_TIMESTAMP - MIN($Fld) AS days_ago
> , MIN($Fld) as oldest_date
> , COUNT(*) AS cnt
> FROM $Tbl;"
> psql -h <host> <db> -Xc "${Sql}"
> days_ago | oldest_date | cnt
> ----------+-------------+-----
> | | 0
> (1 row)
>
> More stuff happens after this. I want to print out these four lines, but
> also test the COUNT(*) value and bypass other bash commands if the count ==
> 0.
>
> Is there any relatively simple way to do this (psql print and return
> count(*))?
1. Don't interpolate $variables to your sql. Instead use -v "t=$Tbl" -v
"f=$Fld" options to psql, and then make your query use :"t" and :"f"
- it's safer.
2. Catch output of the psql to variable, and check last line, there is
rowcount there.
Example:
=$ cat z.sh
#!/usr/bin/env bash
table=pg_stat_all_tables
field=last_autovacuum
returned="$( psql -d depesz_explain -v "t=$table" -v "f=$field" -X << _SQL_
select current_timestamp - min(:"f") as days_ago
, min(:"f") as oldest_date
, count(*) as cnt
from :"t"
_SQL_
)"
row_count="$( tail -n1 <<< "${returned}" | tr -cd 0-9 )"
echo "Rowcount = $row_count"
echo "Full output:"
echo "${returned}"
# vim: set filetype=bash shiftwidth=4 expandtab smarttab softtabstop=4
tabstop=4 textwidth=132 :
=$ bash z.sh
Rowcount = 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)
Best regards,
depesz
