escape code

The shell is a high quality text processor, and zsh is especially suited for that purpose. In this post, I will show some of the tricks I use for an editing problem that I encounter every day. Concretely: How to quickly generate queries, after receiving a list of IDs. This is what the result should look like:

select * from table where ID in ('1234', '2345', '3456');

A fast way to achieve this looks thusly:

% ar=(
array> 1234
array> 2345
array> 3456
array> )

% print "select * from table where id in (${(j:, :)${(qq)ar[@]}});"
select * from table where id in ('1234', '2345', '3456');

What is happening here? First I am creating an array of something I have available in my paste buffer: I type the name of an array, =, and (, and press enter. Then I paste a list of IDs, close it off with a ). Now the info is available at my fingertips. The second action, the print statement with the hardly memorable parameter expansion, is the main topic of this post.

Shells provide convenience functions to do stuff with parameters, and zsh surely is the most advanced in this regard. These parameter expansions are easiest to read from the inside to the outside, so let's have a look at ${(qq)ar[@]}. This consists of two parts, ${ar[@]} and (qq).

The result of ${ar[@]} is into all the elements of array ar. In any shell that conforms to POSIX, you can specify elements of an array by encapsulating them in square brackets: ${ar[2]} would be 2345. One can use the @ to say that you want all the indexes.

In zsh, if there is an opening bracket directly after the curly opening bracket, magic is immanent. The bracketed characters are flags. For a complete overview of what can be done, see man zshexpn | less +/'^ *Parameter Expansion Flags'. In this case, the members of ar are treated with the action that is hiding behind the qq. The effect of this flag is quoted with single quotes. (You can use three q's for double quotes).

So the net result of the inner expansion is a copy of the arrat ar, with the difference that the elements are quoted. This is the intermediate result what the outer expansion, ${(j:, :)…} is working with. The flag j is for joining the elements of an array, with whatever is between the colons as a separator, in our case a comma followed by space. The colons are arbitrary: If your join string contains colons itself, you can take a comma or a period, or whatever.

The result is, as you have seen, that ${(j:, :)${(qq)ar[@]}} is expanded to a comma separated line of quoted elements of array ar. As I use this kind of expansion on a daily basis, and this expansion is a bit too tedious to type in every time, I spent a bit of time to make this expansion available at my finger tips in the form of a shell function a2q:

a2q () {
        print ${(j:, :)${(Pqq)1}[@]}
}

This can be used as follows:

% print "select * from table where id in ($(a2q ar));"

Compared to the interactive version, the array is a positional parameter: $1 is being expanded, so you can type: a2q myarray to have a2q work on myarray. In order to make this work, an extra trick needed to be added: The P-flag has been added to the inner parameter expansion flags. This makes that the resulting string is considered to be a parameter.

This works great, but it can be generalized. Sometimes the list of IDs is given as an file. By virtue of the f-flag, the following snippet loads the newline separated contents of file file into array array:

array=( ${(f)$(<file)} )

Just to facilitate laziness, the function a2q can be expanded to check what type of argument it got, and based on that, populate a temporary array with infos. Multiple arguments are allowed. If no argument is provided, a2q will listen to STDIN, so you can pipe the output of another command to it. After all the processing, the last step is to print the contents of the array in the desired way:

emulate -L zsh
typeset -U ar
ar=()
_err() {
    echo "Do not understand: ${1}" >&2
    echo "Arguments need to be files, names of arrays, or standard input." >&2
    echo 'Arrays must be referenced by name, so use `array` instead of `$array`.' >&2
    exit 1
}

if [[ $# == 0 ]]; then
    # Listen to STDIN if no arguments are provided
    ar=( ${(f)$(<&0)} )
fi

while [[ $# > 0 ]]; do
    if [[ -r "$1" && -f "$1" ]]; then
        # A readable file.
        ar=(
            $ar[@]
            "${(fq)$(<$1)}"
        )
    elif [[ ${(Pt)1} = "array" ]]; then
        ar=(
            $ar[@]
            ${(Pq)1}
        )
    else
        _err
    fi
    shift
done

print ${(j:, :)${(qq)ar[@]}}

I stored the above as a2q in a directory whose contents gets autoloaded when required, so I have it available at my finger tips.

Enjoy!