1

I have the following SQL query that executes correctly in the psql shell:

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;

I am trying to figure out how to pass it as an argument in a Bash shell and in a Bash script.

I tried to use HEREDOC, but it looks that it is not passed as an argument to 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.

I also tried to pass the query as a variable, but it does not seem to work as well. Variable:

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;

Output:

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
xgkphdx
  • 11

1 Answers1

0

I am not sure why backslash escaping did not work for me initially, but I got it working after putting \ in front of the double quotes inside the SQL query.

So, the following command is working for me:

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
xgkphdx
  • 11