无法在 bash 中循环访问数组以进行 PostgreSQL 查询导出

Question

我有一个PostgreSQL查询，我想通过循环为多个地理区域运行。 我想使用数组中的元素来修改查询和我要将数据导出到的 csv 文件的名称。所以从本质上讲，我希望查询在...cwa = 'MFR'...并导出到hourly_MFR.csv，然后继续运行...cwa = 'PQR'...并导出到hourly_PQR.csv，依此类推。

这是我到目前为止所拥有的。我想脚本中的 EOF 可能会导致问题，但我无法弄清楚如何在保持脚本的一般格式的同时让循环工作。

此外，没有循环的查询/脚本(不包括声明、for、do、done 语句(工作正常。

dbname="XXX"
username="XXXXX"
psql $dbname $username << EOF
declare -a arr=('MFR', 'PQR', 'REV')
for i in "${arr[@]}"
do
COPY
(SELECT d.woyhh,
COALESCE(ct.ct, 0) AS total_count
FROM
(SELECT f_woyhh(d::TIMESTAMP) AS woyhh
FROM generate_series(TIMESTAMP '2018-01-01', TIMESTAMP '2018-12-31', interval '1 hour') d) d
LEFT JOIN
(SELECT f_woyhh((TIME)::TIMESTAMP) AS woyhh,
count(*) AS ct
FROM counties c
JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom)
WHERE cwa = $i
GROUP BY 1) ct USING (whh)
ORDER BY 1) TO /var/www/html/GIS/ltg_db/bigquery/hourly_$i.csv CSV HEADER;
done
EOF

感谢您的任何帮助！

Answer 1

我想你快到了，你只需要重新排序一些行。试试这个：

dbname="XXX"
username="XXXXX"
declare -a arr=('MFR', 'PQR', 'REV')
for i in "${arr[@]}"
do
psql $dbname $username << EOF
COPY
(SELECT d.woyhh,
COALESCE(ct.ct, 0) AS total_count
FROM
(SELECT f_woyhh(d::TIMESTAMP) AS woyhh
FROM generate_series(TIMESTAMP '2018-01-01', TIMESTAMP '2018-12-31', interval '1 hour') d) d
LEFT JOIN
(SELECT f_woyhh((TIME)::TIMESTAMP) AS woyhh,
count(*) AS ct
FROM counties c
JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom)
WHERE cwa = $i
GROUP BY 1) ct USING (whh)
ORDER BY 1) TO /var/www/html/GIS/ltg_db/bigquery/hourly_$i.csv CSV HEADER;
EOF
done

declare和for循环是bash脚本的一部分，而<<EOF和EOF之间的所有内容都是Postgresql查询的一部分。

Answer 2

在上面的 Woitok 的回答@Lienhart，解决方案肯定会奏效。 但是 - 请注意，这会产生执行新的"psql"调用、数据库连接设置、身份验证和返回的后续响应的副作用;然后关闭连接 - 对于循环的每次迭代。

在这种情况下，您只运行循环的 3 次迭代，因此这可能不是一个重大问题。 但是，如果扩展用法以运行更多迭代，则可能需要对其进行优化，以仅运行单个数据库连接并对其进行批量查询。

为此，可能需要使用临时工作文件来构建 SQL 命令。 还有其他方法，但这使用和调试相对简单：

QUERY_FILE=$(mktemp /tmp/query.XXXXXXX)
# note the use of an array isn't really necessary in this use
# case - and a simple set of values can be used equally as well
CWA="MFR PQR REV"
for i in $CWA
do
cat <<EOF >> $QUERY_FILE
<ADD_YOUR_QUERY_STATEMENTS_HERE>
EOF
done
psql --file=$QUERY_FILE $dbname $username
if (( $? )) 
then
echo "query failed (QUERY_FILE: ($QUERY_FILE')"
exit 1 
else
echo "query succeeded"
rm -f $QUERY_FILE
exit 0
fi