1

I have a list of files that I want to search across a list of database tables:

[root@host hs]# head -n 3 tables
rec_playinth120116
rec_playinth120216
rec_playinth120316
[root@host hs]# head -n 3 files
0128184628OV30.wav
0128780332OV30.wav
0128439969OV30.wav

I'm trying to simplify the process by creating a simple shell script that just looks at the input files and outputs the results to a third file containing the full path of each file. Essentially this (the sed cleans up the output into a normal file path that can be used as input in another process):

psql -d task_hst -A -P tuples_only=on -c "select f_path, file_name from $TABLES where file_name = ''$FILES''"|sed 's/|/\//g' >> $OUT

When this is run in a for loop that obviously doesn't compare each file against each table. The only way I've been able to get the desired output is to hard-code each table and run them as individual commands, i.e.:

for x in $FILE
do

psql -d task_hst -A -P tuples_only=on -c "select f_path, file_name from rec_playinth120116 where file_name = '$x'"|sed 's/|/\//g' >> $OUT
psql -d task_hst -A -P tuples_only=on -c "select f_path, file_name from rec_playinth120216 where file_name = '$x'"|sed 's/|/\//g' >> $OUT
psql -d task_hst -A -P tuples_only=on -c "select f_path, file_name from rec_playinth120316 where file_name = '$x'"|sed 's/|/\//g' >> $OUT

This is not the most elegant or user-friendly way of doing things.

Ideally, the files names would be inputs passed when running the script, something like this:

while true
do
        read -r f1 <&3 || break
        read -r f2 <&4 || break
        psql -d task_hst -A -P tuples_only=on -c "select f_path, file_name from $f1 where file_name = '$f2'"|sed 's/|/\//g' >> $OUT
done 3<$1 4<$2

However, that will break after the first result is found, when I need it to continue searching across all the tables specified.

I know there is a way to do this with while or paste or something, but it's outside of my skill-set. Is there a simple way to accomplish this?

  • Sorry for not getting this properly. Are you essentially attempting to have the Cartesian product of contents of the two files as input? – Barun Dec 27 '16 at 15:16
0

@barun Yes that's exactly it, though I did not know "Cartesian product" was the term to describe it. I did some research with that information and came up with this:

while read line1
do
    while read line2
    do psql -d task_hst -A -P tuples_only=on -c "select f_path, file_name from $line1 where file_name = '$line2'"|sed 's/|/\//g' >> $OUT
    done < $2
done < $1

That seems to do get the job done. Thank you for your response! Is there a better way of doing this?

  • Glad that you figured it out. However, not sure about any better alternative. – Barun Dec 27 '16 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.