How can a bash function return multiple values?
Yes, bash
's return
can only return numbers, and only integers between 0 and 255.
For a shell that can return anything (lists of things), you can look at es
:
$ es -c "fn f {return (a 'b c' d \$*)}; printf '%s\n' <={f x y}"
a
b c
d
x
y
Now, in Korn-like shells like bash
, you can always return the data in a pre-agreed variable. And that variable can be in any type supported by the shell.
For bash
, that can be scalar, sparse arrays (associative arrays with keys restricted to positive integers) or associative arrays with non-empty keys (neither key nor values can contain NUL characters).
See also zsh
with normal arrays and associative arrays without those restrictions.
The equivalent of the f
es
function above could be done with:
f() {
reply=(a 'b c' d "$@")
}
f
printf '%s\n' "${reply[@]}"
Now, mysql
queries generally return tables, that is two-dimensional arrays. The only shell that I know that has multi-dimensional arrays is ksh93
(like bash
it doesn't support NUL characters in its variables though).
ksh
also supports compound variables that would be handy to return tables with their headers.
It also supports passing variables by reference.
So, there, you can do:
function f {
typeset -n var=$1
var=(
(foo bar baz)
(1 2 3)
}
}
f reply
printf '%s\n' "${reply[0][1]}" "${reply[1][2]}"
Or:
function f {
typeset -n var=$1
var=(
(firstname=John lastname=Smith)
(firstname=Alice lastname=Doe)
)
}
f reply
printf '%s\n' "${reply[0].lastname}"
Now, to take the output of mysql
and store that in some variables, we need to parse that output which is text with columns of the table separated by TAB characters and rows separated by NL and some encoding for the values to allow them to contain both NL and TAB.
Without --raw
, mysql
would output a NL as \n
, a TAB as \t
, a backslash as \\
and a NUL as \0
.
ksh93
also has read -C
that can read text formatted as a variable definition (not very different from using eval
though), so you can do:
function mysql_to_narray {
awk -F '\t' -v q="'" '
function quote(s) {
gsub(/\\n/, "\n", s)
gsub(/\\t/, "\t", s)
gsub(/\\\\/, "\\", s)
gsub(q, q "\\" q q, s)
return q s q
}
BEGIN{print "("}
{
print "("
for (i = 1; i <= NF; i++)
print " " quote($i)
print ")"
}
END {print ")"}'
}
function query {
typeset -n var=$1
typeset db=$2
shift 2
typeset -i n=0
typeset IFS=' '
typeset credentials=/path/to/file.my # not password on the command line!
set -o pipefail
mysql --defaults-extra-file="$credentials" --batch \
--skip-column-names -e "$*" "$db" |
mysql_to_narray |
read -C var
}
To be used as
query myvar mydb 'select * from mytable' || exit
printf '%s\n' "${myvar[0][0]}"...
Or for a compound variable:
function mysql_to_array_of_compounds {
awk -F '\t' -v q="'" '
function quote(s) {
gsub(/\\n/, "\n", s)
gsub(/\\t/, "\t", s)
gsub(/\\\\/, "\\", s)
gsub(q, q "\\" q q, s)
return q s q
}
BEGIN{print "("}
NR == 1 {
for (i = 1; i<= NF; i++) header[i] = $i
next
}
{
print "("
for (i = 1; i <= NF; i++)
print " " header[i] "=" quote($i)
print ")"
}
END {print ")"}'
}
function query {
typeset -n var=$1
typeset db=$2
shift 2
typeset -i n=0
typeset IFS=' '
typeset credentials=/path/to/file.my # not password on the command line!
set -o pipefail
mysql --defaults-extra-file="$credentials" --batch \
-e "$*" "$db" |
mysql_to_array_of_compounds |
read -C var
}
To be used as:
query myvar mydb 'select "First Name" as firstname,
"Last Name" as lastname from mytable' || exit
printf '%s\n' "${myvar[0].firstname"
Note that the header names (firstname
, lastname
above) have to be valid shell identifiers.
In bash
or zsh
or yash
(though beware array indices start at 1 in zsh and yash and only zsh
can store NUL characters), you could always return one array per column, by having awk
generate the code to define them:
query() {
typeset db="$1"
shift
typeset IFS=' '
typeset credentials=/path/to/file.my # not password on the command line!
set -o pipefail
typeset output
output=$(
mysql --defaults-extra-file="$credentials" --batch \
-e "$*" "$db" |
awk -F '\t' -v q="'" '
function quote(s) {
gsub(/\\n/, "\n", s)
gsub(/\\t/, "\t", s)
gsub(/\\\\/, "\\", s)
gsub(q, q "\\" q q, s)
return q s q
}
NR == 1 {
for (n = 1; n<= NF; n++) column[n] = $n "=("
next
}
{
for (i = 1; i < n; i++)
column[i] = column[i] " " quote($i)
}
END {
for (i = 1; i < n; i++)
print column[i] ") "
}'
) || return
eval "$output"
}
To be used as:
query mydb 'select "First Name" as firstname,
"Last Name" as lastname from mytable' || exit
printf '%s\n' "${firstname[1]}"
Add a set -o localoptions
with zsh
or local -
with bash4.4+ before the set -o pipefail
for the setting of that option to be local to the function like with the ksh93
approach.
Note that in all the above, we're not converting back the \0
s to real NULs as bash
or ksh93
would choke on them. You may want to do it if using zsh
to be able to work with BLOBs but note that the gsub(/\\0/, "\0", s)
would not work with all awk
implementations.
In any case, here, I'd use more advanced languages than a shell like perl or python to do this kind of thing.