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? — fphilipe
– fphilipe, Commented Jul 10, 2019 at 6:04

jottbe · Accepted Answer · 2019-07-09 22:08:39Z

1

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.

answered Jul 9, 2019 at 22:08

jottbe

4,5464 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related