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?

1
  • 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, 2016 at 15:16

1 Answer 1

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?

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .