Add multiple and various content from bash parameter to a variable set into the sql script called

Question

I have a SQL script and a sh executable to run a script doing some operations on my database.

My principal problem is I'm searching how I could do the following thing:

Send an array of parameter from my bash parameters when launching the script, my actual command is:

./myscript.sh databaseName user thirdParameterToPassAsAString 
'fourthParameterElement1','fourthParameterElement2','fourthParameterElement3'

the content of my script:

#!/bin/bash

set -e

psql $1 $2 <<EOF
    set search_path = search_path;
    set firstParameterusedinSQLScript = $3;
    set Param_List = $4;
    \i my_script.sql
EOF

and the sql part where I have the problem:

where ae.example in (:Param_List)

I have of course some issues with this where clause.

So the question is how could i do this?

Possible duplicate of [How do you use script variables in psql?](https://stackoverflow.com/questions/36959/how-do-you-use-script-variables-in-psql) — fphilipe, Jul 10 '19 at 06:04

score 1 · Accepted Answer · answered Jul 09 '19 at 22:08

Have you considered changeing the sql itself (not changeing the original sql file that contains it) before executing it (replaceing the parameter via sed). If that is an option for you, you could define a helpber function like

function prepare_script() {
    cat  <<EOF
    set search_path = search_path;
EOF
    sed -e"s|:Param_List|$3|g" -e"s|firstParameterusedinSQLScript|$2|g" Requetes_retour_arriere_fiab_x_siret.sql
}

You could then call it like:

prepare_script "$1" "$2" "$3" | psql $1 $2

Note, that you do not change the file on disk itself, you just read it using set and have it output the altered sql on stdout and pipe it to psql.

Add multiple and various content from bash parameter to a variable set into the sql script called

1 Answers1