☰

bash - 将sql查询作为参数传递给psql

tag-icon • tag-icon linux command-line bash sql psql
bash - 将sql查询作为参数传递给psql

我有以下在 psql shell 中正确执行的 SQL 查询：

SELECT c.component_name AS "Component",
       c.component_version_name AS "Component Version",
       c.version_origin_id AS "Version Origin ID" 
FROM reporting.component c 
JOIN reporting.component_license cl 
ON cl.component_table_id = c.id 
WHERE cl.license_display = 'Unknown License' 
GROUP BY c.component_name, c.component_version_name, c.version_origin_id;

我正在尝试弄清楚如何在 Bash shell 和 Bash 脚本中将其作为参数传递。

我尝试使用 HEREDOC，但它看起来没有作为参数传递给 psql：

psql -d bds_hub -c <<SQL_QUERY
SELECT c.component_name AS "Component",
       c.component_version_name AS "Component Version",
       c.version_origin_id AS "Version Origin ID" 
FROM reporting.component c 
JOIN reporting.component_license cl 
ON cl.component_table_id = c.id 
WHERE cl.license_display = 'Unknown License' 
GROUP BY c.component_name, c.component_version_name, c.version_origin_id;
SQL_QUERY
psql: option requires an argument: c
Try "psql --help" for more information.

我也尝试将查询作为变量传递，但似乎效果不佳。变量：

echo $SQL_QUERY
SELECT c.component_name AS "Component",
       c.component_version_name AS "Component Version",
       c.version_origin_id AS "Version Origin ID" 
FROM reporting.component c 
JOIN reporting.component_license cl 
ON cl.component_table_id = c.id 
WHERE cl.license_display = 'Unknown License' 
GROUP BY c.component_name, c.component_version_name, c.version_origin_id;

输出：

psql -d bds_hub -c $SQL_QUERY
psql: warning: extra command-line argument "AS" ignored
psql: warning: extra command-line argument ""Component",c.component_version_name" ignored
psql: warning: extra command-line argument "AS" ignored
psql: warning: extra command-line argument ""Component" ignored
psql: warning: extra command-line argument "Version",c.version_origin_id" ignored
psql: warning: extra command-line argument "AS" ignored
psql: warning: extra command-line argument ""Version" ignored
psql: warning: extra command-line argument "Origin" ignored
psql: warning: extra command-line argument "ID"" ignored
psql: warning: extra command-line argument "FROM" ignored
psql: warning: extra command-line argument "reporting.component" ignored
psql: warning: extra command-line argument "c" ignored
psql: warning: extra command-line argument "JOIN" ignored
psql: warning: extra command-line argument "reporting.component_license" ignored
psql: warning: extra command-line argument "cl" ignored
psql: warning: extra command-line argument "ON" ignored
psql: warning: extra command-line argument "cl.component_table_id" ignored
psql: warning: extra command-line argument "=" ignored
psql: warning: extra command-line argument "c.id" ignored
psql: warning: extra command-line argument "WHERE" ignored
psql: warning: extra command-line argument "cl.license_display" ignored
psql: warning: extra command-line argument "=" ignored
psql: warning: extra command-line argument "'Unknown" ignored
psql: warning: extra command-line argument "License'" ignored
psql: warning: extra command-line argument "GROUP" ignored
psql: warning: extra command-line argument "BY" ignored
psql: warning: extra command-line argument "c.component_name," ignored
psql: warning: extra command-line argument "c.component_version_name," ignored
psql: warning: extra command-line argument "c.version_origin_id;" ignored
psql: FATAL:  role "c.component_name" does not exist

答案1

我不确定为什么反斜杠转义最初对我来说不起作用，但是\在 SQL 查询中将其放在双引号前面后我让它起作用了。

因此，以下命令对我有用：

psql -d bds_hub -P pager=off \
> -c \
>"SELECT c.component_name as \"Component Name\",c.component_version_name \
> AS \"Component Version\",\
> c.version_origin_id AS \"Origin ID\" \
> FROM reporting.component c \
> JOIN reporting.component_license cl \
> ON cl.component_table_id = c.id \
> WHERE cl.license_display = 'Unknown License' \
> GROUP BY c.component_name, c.component_version_name, c.version_origin_id;" > license.txt

相关内容