问题描述
我想计算特定模式中存在的表中的行数。我已经计算了表的行数,但我想以以下格式输出:-
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
格式和字符
(这是字符串常量的特殊语法,请比较echo a\tb; echo -e a\tb; echo $'a\tb '
) -
读取ab
:读取输入,并以$ IFS
放入变量
-
<<
:使用后面的值作为上一条命令的输入
- ...等等
-X
psql
option: do not use the~/.psqlrc
file to avoid unnecessary outputcopy (<query>) to stdout with (format csv, delimiter e'\\t')
: output the result of<query>
usingCSV
format with character as delimiterIFS=$'\t'
: Temporary set the default shell delimiter to character
(here is special syntax for string constants, compareecho "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
这篇关于如何在特定模式下的表中打印总行数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!