Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1vaAEO-004YuH-00
	for pgsql-general@arkaria.postgresql.org;
	Mon, 29 Dec 2025 10:12:05 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.96)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1vaAEM-00GQMg-3C
	for pgsql-general@arkaria.postgresql.org;
	Mon, 29 Dec 2025 10:12:03 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <depesz@depesz.com>)
	id 1vaAEM-00GQMX-1r
	for pgsql-general@lists.postgresql.org;
	Mon, 29 Dec 2025 10:12:03 +0000
Received: from lana.depesz.com ([88.198.49.178] helo=depesz.com)
	by makus.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <depesz@depesz.com>)
	id 1vaAEL-003A03-1c
	for pgsql-general@postgresql.org;
	Mon, 29 Dec 2025 10:12:02 +0000
DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=depesz.com;
	s=20170201; h=In-Reply-To:Content-Type:MIME-Version:References:Reply-To:
	Message-ID:Subject:Cc:To:Sender:From:Date:Content-Transfer-Encoding:
	Content-ID:Content-Description;
	bh=0bEDKebLq4oAPqA7lsuCuC7fW3Z4iMfNi5RMteXZMpE=; b=uGWqSu5nwB4qbhDNNVtIb8l6AO
	uovfYpjNXFMXB15zHNtll7jRT/T8TNe8jA20jTEEU5g6hjE4N5tLL/3oRkMhOAFvjivOP5Re+rsbd
	jfndf21zE8gB103OEjKtOrpnPsmtkLTp3S4BzSUb0A/hGAZ75IvDvzfLUI65Bx1eeAj8=;
Received: from depesz by depesz.com with local (Exim 4.96)
	(envelope-from <depesz@depesz.com>)
	id 1vaAEI-009g75-1A;
	Mon, 29 Dec 2025 11:11:58 +0100
Date: Mon, 29 Dec 2025 11:11:58 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: psql: print values and return the COUNT(*) value to bash?
Message-ID: <aVJT7n_f7kEjO0Qb@depesz.com>
Reply-To: depesz@depesz.com
References: <CANzqJaBX-SZZcHn71xgqtSKx6ekZGdHPSgCmP2co5=-KDu4Q-Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <CANzqJaBX-SZZcHn71xgqtSKx6ekZGdHPSgCmP2co5=-KDu4Q-Q@mail.gmail.com>
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/aVJT7n_f7kEjO0Qb%40depesz.com>
Precedence: bulk

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