如何在特定模式下的表中打印总行数？ [英] How to print the total number of row count in tables in specific schema?

问题描述

我想计算特定模式中存在的表中的行数。我已经计算了表的行数，但我想以以下格式输出：-

i want to calculate the number of row counts in tables present in specific schema.i have calculated the row count of the tables but i want output in format:-

Total number of Row Count for table_name is :[value]

我准备了脚本，在脚本中，我可以获取架构中存在的每个表的计数。但我担心的是，我想要这样的输出：-

I have prepared the scripts in which i an getting the count of every table present in the schema. But my concern is i want the output like :-

total no. of row count in table_name is [value].

类似这样的东西

#!/bin/bash

databasename="$(cat /home/enterprisedb/.bash_profile |grep PGDATABASE|awk '{print $1}'|cut -d '=' -f2|cut -d ';' -f1)"
echo "$databasename"

listof_table="select t.table_name from information_schema.tables t where t.table_schema = 'emp_history' and t.table_type = 'BASE TABLE' order by t.table_name;"

listof_schema="select schema_name from information_schema.schemata where schema_name ='emp_history';"

query_count="select 'select count(*) from ' || tablename || ' ;' from pg_tables where schemaname='emp_history';"

var2="$(psql -U enterprisedb -d $databasename -c "$listof_schema" -L /home/enterprisedb/schema_name.log)"

sed -i -e "1,6d" /home/enterprisedb/schema_name.log

final_schema_list="$(cat /home/enterprisedb/schema_name.log| head -1|tr -d "[:blank:]" > /home/enterprisedb/final_list.log)"

count="$(cat /home/enterprisedb/final_list.log)"

echo "$count"

var1="$(psql -U enterprisedb -d $databasename -c "$listof_table" -L /home/enterprisedb/table_name.log)"

sed -i -e "1,6d" -e '$d' /home/enterprisedb/table_name.log

table_name="$(cat /home/enterprisedb/table_name.log |tr -d "[:blank:]" > /home/enterprisedb/table_name_final.log)"

table_name1="$(cat /home/enterprisedb/table_name_final.log )"

final_table_name="$(cat /home/enterprisedb/table_name_final.log|head -n -1 > /home/enterprisedb/final.log)"

table_name_final="$(cat /home/enterprisedb/final.log)"

echo "$table_name_final"
for i in $table_name_final
do
        table_count="$(psql -U enterprisedb -d $databasename -c "select count(*) from "$count"."$i";")"
        echo "Total number of Row Count for "$i" is : "$table_count""

done

我想要这样的结果：-

Total number of Row Count for table_name is :[value]

但实际上它的打印输出像：-

But actually its printing output like:-

 count
-------
     2
(1 row)

Total number of Row Count for mail_template is :
 count
-------
     1
(1 row)

Total number of Row Count for mail_template_key is :
 count
-------
     7
(1 row)

Total number of Row Count for v_biel_kategorie is :
 count
-------
    40
(1 row)

Total number of Row Count for v_sample_type is :

先打印计数，然后再打印表名称。

print the count first then table name.

推荐答案

在shell脚本中如何使用 psql 输出的常用方法。

The common approach how to use psql output in shell scripts.

对于单行输出：

IFS=$'\t' read a b <<< $(psql -X -c "copy (select 1, random()) to stdout with (format csv, delimiter e'\\t')")
echo "a=$a, b=$b"

对于多行输出：

TEMP_IFS=$IFS
IFS=$'\t'
while read a b; do
    echo "a=$a, b=$b"
done <<< $(psql -X -c "copy (select i, random() from generate_series(1,5) as i) to stdout with (format csv, delimiter e'\\t')")
IFS=$TEMP_IFS
echo "a=$a, b=$b"

（注意 $ a 和 $ b 变量的值在期间不可用块）

(Note that the values of $a and $b variables is not available outside of while block)

此处：

• -X psql 选项：请勿将〜/ .psqlrc 文件用于避免不必要的输出
  


• 将副本（< query>）复制到标准输出，格式为（csv，分隔符e'\\t'）：使用 CSV 格式和 Tab < query> 的结果>字符作为分隔符
  


• IFS = $'\t'：临时将默认的外壳分隔符设置为 Tab 字符
  （这是字符串常量的特殊语法，请比较 echo a\tb; echo -e a\tb; echo $'a\tb '）


• 读取ab ：读取输入，并以 $ IFS 放入变量
  


• << ：使用后面的值作为上一条命令的输入
  


• ...等等

• -X psql option: do not use the ~/.psqlrc file to avoid unnecessary output
  
• copy (<query>) to stdout with (format csv, delimiter e'\\t'): output the result of <query> using CSV format with Tab character as delimiter
  
• IFS=$'\t': Temporary set the default shell delimiter to Tab character
  (here is special syntax for string constants, compare echo "a\tb"; echo -e "a\tb"; echo $'a\tb')
• read a b: read input delimited by $IFS into the variable(s)
  
• <<<: use the followed value as input to the previous command
  
• ... etc

这篇关于如何在特定模式下的表中打印总行数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！