Heroku PSQL pass dynamic parameter to SQL file

I am trying to execute a .sql file in Heroku PSQL and want to pass dynamic parameter values in the .sql file.

Below is the script which I am using

heroku pg:psql --app application_name <./somepath/file_to_execute.sql --param1="'$File_name'" --param2="'$Tag_id'" --param3="'$job_name'" --param4="$id"

The sql file contains insert script:

INSERT INTO version_table (col1, col2, col3, col4) 
VALUES (:param1,:param2,:param3,:param4);

I get below error message from Heroku:

Error: Unexpected arguments: --param2='1.1.1', --param3='test-name', --param4=12

How to execute this sql file with dynamic value in Heroku PSQL

I also tried below query:

heroku pg:psql --app application_name <./somepath/file_to_execute.sql --v param1="'$File_name'" --v param2="'$Tag_id'" --v param3="'$job_name'" --v param4="$id"

Got below error message:

Error: Unexpected arguments: param1='file_name.sql', --v, param2='1.1.1', --v, param3='test-name', --v, param4=12

Solution

You can get the URL to the database with the following heroku command:

$ heroku pg:credentials:url --app application_name

This will print something like:

Connection information for default credential.
Connection info string:
   "dbname=xyz host=something.compute.amazonaws.com port=1234 user=foobar password=p422w0rd sslmode=require"
Connection URL:
   postgres://foobar:p422w0rd@something.compute.amazonaws.com:1234/xyz

The URL (last line) can be used directly with psql. With grep we can get that line and pass it to psql:

$ psql $(heroku pg:credentials:url --app application_name | grep 'postgres://') \
    < ./somepath/file_to_execute.sql \
    -v param1="$File_name" \
    -v param2="$Tag_id" \
    -v param3="$job_name" \
    -v param4="$id"

Note that rather than putting single quotes in the parameter on the command line, you should access the parameter in the SQL as :'param1'.