1

I'm trying to query the total number of records across multiple PostgreSQL tables in a shell script.

The shell script is to be executed by a build pipeline and the pipeline needs to know the number of total number of rows.

shell script

docker run -it postgres psql "host=xxx.com port=5432 dbname=xxx user=xxx password=xxxw" -f row_count.sql

row_count.sql

select SUM(row_count)
from (
    SELECT count(*) as row_count
    FROM "table_a"
    union all
    SELECT count(*) as row_count
    FROM "table_b"
    union all
    SELECT count(*) as row_count
    FROM "table_c"
) as total

I have tested my query and it's fine, but having difficulty reading the sql file from the psql command and I'm not sure how to save the number of records in a variable to be read by the pipeline.

Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32
shrek_23
  • 51
  • 1
  • 5

1 Answers1

0

to execute a command in an shell script, use backticks. https://unix.stackexchange.com/questions/27428/what-does-backquote-backtick-mean-in-commands So your command will look like that:

PGRESULT=`docker run -it postgres psql "host=xxx.com port=5432 dbname=xxx user=xxx password=xxxw" -f row_count.sql`

the result is stored in PGRESULT. Read the manpage to psql, maybe adding some parameters to psql helps further working with values in PGRESULT.

-t --tuples-only Turn off printing of column names and result row count footers, etc. This is equivalent to \t or \pset tuples_only.

-c command --command=command Specifies that psql is to execute the given command string, command. This option can be repeated and combined in any order with the -f option. When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence.

-A --no-align Switches to unaligned output mode. (The default output mode is otherwise aligned.) This is equivalent to \pset format unaligned.

-F separator --field-separator=separator Use separator as the field separator for unaligned output. This is equivalent to \pset fieldsep or \f.

FatFreddy
  • 1,160
  • 1
  • 9
  • 16
  • Thanks I have decided to incorporated that in a psql function https://stackoverflow.com/questions/63008908/plpgsql-function-return-true-the-number-of-records-across-multiple-tables-great – shrek_23 Jul 21 '20 at 06:51