2

There are a lot of variations on this question, but I wanted to ask it here anyways because I am so close (!) to finding a solution on this one - I just need a little nudge.

Scenario

I have several text SQL files that I use to run reports on a Postgres database hosted with Heroku. One of the SQL files currently looks like this:

SELECT
  (SELECT COUNT(*) FROM metrics WHERE(organization_id = o.id AND year = ${sql_year})) AS metrics,
  (SELECT COUNT(*) FROM my_metrics WHERE(organization_id = o.id)) AS quarterly_metrics,
  (SELECT COUNT(*) FROM quarterly_metrics WHERE(organization_id = o.id AND year = ${sql_year})) AS quarterly_metrics,
  (SELECT COUNT(*) FROM quarterly_text_metrics WHERE(organization_id = o.id AND year = ${sql_year})) AS quarterly_text_metrics,
  (SELECT COUNT(*) FROM budgets WHERE(organization_id = o.id AND year = ${sql_year})) AS budgets,
  (SELECT COUNT(*) FROM goals WHERE(goalable_type = 'Organization' AND goalable_id = o.id AND year = ${sql_year})) AS goals
FROM
  organizations AS o
WHERE
  o.id = ${sql_org_id}
;

I would like to use a process like this:

export sql_org_id=1; export sql_year=2013
cat reports/my_report.sql | sed -e "s/\(\${[a-zA-Z_]*}\)/`eval "echo '\1'"`/" | heroku pg:psql

So that I can just pipe the SQL to Heroku for the report, replacing all the variables with their corresponding ENV vars that were exported just before running the script (so that I can simply change the variables on the fly and run additional reports instead of editing the SQL file).

My Problem

So the issue is that I am almost there, but I can't quite get it. The sed expression I have works when I use echo like this:

export sql_year=2013
echo "SELECT ${sql_year} AS year" | sed -e "s/\(\${[a-zA-Z_]*}\)/`eval "echo '\1'"`/"

But I can't get it to work with cat on the entire file contents:

export sql_org_id=1; export sql_year=2013
cat reports/my_report.sql | sed -e "s/\(\${[a-zA-Z_]*}\)/`eval "echo '\1'"`/"

Am I missing something when using sed with cat? Please help!

UPDATE: I am on Mac OS X 10.8

Improve this question
3
  • Have you tried abandoning cat altogether and just using the sed -e 'expression_here' file_name syntax? You're guilty of a useless use of cat there. Also, it may not be necessary with the particular example you specified but semantically, I think you need a g modifier on your sed regex. Commented Jul 31, 2013 at 23:28
  • I don't think you can execute code inside of a sed either. It was covered earlier in this question today: unix.stackexchange.com/questions/85082/… Commented Jul 31, 2013 at 23:33
  • @slm: You can execute code inside of a sed. Try echo foo | sed "s/foo/$(uptime)/" Commented Aug 1, 2013 at 4:51

3 Answers 3

Reset to default
1

With zsh:

print -r -- "${(e)$(<reports/my_report.sql)}"

With any Bourne-like shell (including zsh):

eval "cat << EOF
$(cat reports/my_report.sql)
EOF"

Both will expand all the `...`, $(...), $((...)), $[...], $xxx and ${xxx}. So you'll need to escape the dollar and backtick characters you don't want expanded.

In addition, for the second one, the content of the file must not contain a line consisting of the three characters EOF (which for SQL shouldn't be a problem).

Alternatively, you could do:

perl -pe 's/\${(\w+)}/$ENV{$1}/g' reports/my_report.sql

You'll need to export the variables, for perl to be aware of them.

As to why your solution doesn't work and is not anywhere near working, you can run it after set -x to see what's going on:

$ echo "SELECT ${sql_year} AS year" | sed -e "s/\(\${[a-zA-Z_]*}\)/`eval "echo '\1'"`/"
+ echo 'SELECT 2013 AS year'
++ eval 'echo '\''\1'\'''
+++ echo '\1'
+ sed -e 's/\(${[a-zA-Z_]*}\)/\1/'
SELECT 2013 AS year

By using double quotes, the shell expands the variables and command substitution before calling echo and sed. So, you actually ran:

echo 'SELECT 2013 AS year' | sed -e 's/\(${[a-zA-Z_]*}\)/\1/'

sed cannot execute code. So even if you had used single quotes, sed would not have expanded the `...`.

Improve this answer
1
  • Thank you for the very thorough answer and explanation on what's actually going on. I decided to go with the perl solution because it is much more likely to be the same no matter which system it is run on. Commented Aug 1, 2013 at 15:11
1

I would let Bash interpolate the values for you instead of trying to write a regular expression to do the same (or leverage some templating system).

echo $(eval echo $(cat sql.sql))

Where sql.sql contains a valid bash string (so encase it in double quotes):

"SELECT
  (SELECT COUNT(*) FROM metrics WHERE(organization_id = o.id AND year = ${sql_year})) AS metrics,
  (SELECT COUNT(*) FROM my_metrics WHERE(organization_id = o.id)) AS quarterly_metrics,
  (SELECT COUNT(*) FROM quarterly_metrics WHERE(organization_id = o.id AND year = ${sql_year})) AS quarterly_metrics,
  (SELECT COUNT(*) FROM quarterly_text_metrics WHERE(organization_id = o.id AND year = ${sql_year})) AS quarterly_text_metrics,
  (SELECT COUNT(*) FROM budgets WHERE(organization_id = o.id AND year = ${sql_year})) AS budgets,
  (SELECT COUNT(*) FROM goals WHERE(goalable_type = 'Organization' AND goalable_id =     o.id AND year = ${sql_year})) AS goals
FROM
  organizations AS o
WHERE
  o.id = ${sql_org_id}
;"
Improve this answer
1
  • I tried this approach too, but I use zsh, and I get the following error: zsh: no matches found: (SELECT COUNT(*) FROM metrics WHERE(organization_id = o.id AND year = 2013)) Commented Aug 1, 2013 at 1:39
0

What if you change your approach around so that you do this instead:

$ heroku pg:psql --app app_name < file.sql
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.