Add heredoc (<<) to end of alias within function call

Question

I have an alias which is my psql connection string for a database, let's say the alias is this:

alias GQQ='psql "host=$host user=$redshift_uname dbname=$redshift_dbname port=$port pass word=$redshift_pwd"'

This alias exists so I can connect to the database, but I would also like to call it within a function to send simple queries to the database.

In regular terminal I can run first: GQQ <<EOF second: <QUERY> ex. SELECT * FROM table LIMIT 10; and third: EOF, and my query will return results.

I would like to create function which emulates this as follows:

function qredshift() {
GQQ <<EOF
$*
EOF


}

But I am unsure how to formulate this, I have tried shell within shell and adding quotes in various place, what is the proper way to run this?

Answer 1

You need the EOF to be the full contents of the line for it to be taken as delimiter.

qredshift() {
   local IFS=' '
   psql "host=$host user=$redshift_uname dbname=$redshift_dbname port=$port password=$redshift_pwd" <<EOF
$*
EOF
}

When $* is found in a here document, the positional parameters are joined with the first character of $IFS or space depending on the shell. So we set IFS to space so as to have a consistent behaviour between shells. In zsh, you can also use ${(j[ ])@} to explicitly join on spaces.

Instead of a here document, you could use:

printf '%s\n' "$*" | psql...

Or:

printf '%s\n' "$@" | psql...

To pass each of the argument of qreshift as a separate line of input to psql.

Or use zsh-style here-strings (now also supported in bash and a few other shells):

psql... <<< "$*"

psql (assuming that's the postgresql client) can also be given the SQL query to execute with -c, so:

psql -c "$*" ...

For qredshift to take the SQL code either from arguments or stdin if no argument is provided, you could do:

qredshift() {
   local IFS=' '
   psql ${1+"-c$*"} "host=$host user=$redshift_uname dbname=$redshift_dbname port=$port password=$redshift_pwd"
}

Then you could either do:

qredshift 'select * from...'

qredshift <<< 'select * from...'

qredshift << 'EOF'
select * from...
EOF

Note that it's bad practice to pass passwords on the command line, as that's public information within a system.

Doing PGPASSWORD="$redshift_pwd" psql ... where the password is passed via an environment variable rather than in an argument is safer as environment variables are private.