Question 89467 in PostgreSQL Question posted in
The official documentation can be found here.

How to check if a comma separated argument provided to a shell script is a subset of the values of a column in a postgres table, in shell scripting – Postgresql

I have a script that’s taking some comma separated IDs as an argument, and I want to check if they are valid inputs, i.e. they should be a subset of IDs present in a postgres table.

I have this one solution,

ARGUMENTS="value1,value2,value3"
PSQL_COMMAND="SELECT id FROM users;"
OUTPUT=$(psql -U username -d database_name -c "$PSQL_COMMAND" -t)
IFS=',' read -ra VALUES <<< "$OUTPUT"

for value in "${VALUES[@]}"
do
  if echo "$ARGUMENTS" | grep -q -E "(^|,)$value(,|$)"; then
    echo "$value is a subset of $ARGUMENTS"
  else
    echo "$value is not a subset of $ARGUMENTS"
  fi
done

But I was wondering if there’s a better way to do this.

Would appreciate all your answers.

Tags:

3

Answers


  1. 0

    Suggest you use variable names that are more meaningful to the process that is being coded. It provides a form of embedded documentation which makes it easier for code maintenance. You might also want to isolate the two groupings of test results, positive/negative, into separate streams, depending on how you want to use those results for additional downstream processing. Otherwise, you logic is good, crisp and clean.

    My test (and revised) version of your script:

    #!/bin/bash

privileged="value1,value2,value3"

#PSQL_COMMAND="SELECT id FROM users;"
#OUTPUT=$(psql -U username -d database_name -c "$PSQL_COMMAND" -t)
emp_list="value0,value11,value1,value19,value23,value3,value39,value2,value80"
IFS=',' read -ra emp_arr <<< "${emp_list}"

for empID in "${emp_arr[@]}"
do
    if echo "${privileged}" | grep -q -E "(^|,)${empID}(,|$)"; then
        echo "${empID} is member of privileged group [${privileged}]."
    else
        echo "NO_PERMISSION|${empID}" >&2
    fi
done
    Login or Signup to reply.
  2. 0

    You may be able to avoid the call to grep inside your loop by using bash constructs. Here are three options:

    1. Using bash wildcards and equality comparison inside if check:
    #!/bin/bash

privileged="value1,value2,value3"
emp_list="value0,value11,value1,value19,value23,value3,value39,value2,value80"
IFS=',' read -ra emp_arr <<<"${emp_list}"

for emp in "${emp_arr[@]}" ; do
    # using bash wildcard
    if [[ "$privileged" == *"$emp"* ]]; then
        echo "${emp} is member of privileged group [${privileged}]."
    else 
        echo "NO_PERMISSION|${emp}" >&2
    fi
done
    1. Wildcard comparison using case statement instead of if:
    #!/bin/bash

privileged="value1,value2,value3"
emp_list="value0,value11,value1,value19,value23,value3,value39,value2,value80"
IFS=',' read -ra emp_arr <<<"${emp_list}"

for emp in "${emp_arr[@]}" ; do
    # using case statement
    case "$privileged" in
        *"$emp"*)
            echo "[case] ${emp} is member of privileged group [${privileged}]."
            ;;
        *)
            echo "NO_PERMISSION|${emp}" >&2
    esac
done
    1. Using bash regex:
    #!/bin/bash

privileged="value1,value2,value3"
emp_list="value0,value11,value1,value19,value23,value3,value39,value2,value80"
IFS=',' read -ra emp_arr <<<"${emp_list}"

for emp in "${emp_arr[@]}" ; do
    # using bash regex
    if [[ "$privileged" =~ .*"$emp".* ]]; then
        echo "${emp} is member of privileged group [${privileged}]."
    else 
        echo "NO_PERMISSION|${emp}" >&2
    fi
done
    Login or Signup to reply.
  3. 0

    there is a more efficient way as long as you can guarantee a dedicated separation character (here I used the percentage sign) to never ever occur in the values. The code looks a bit cryptic, but it does NOT have a loop.
    The monstrous SED at the end is just for nice display and you maybe just want to filter out the matching >>OR<< the not matching names.

    ARGUMENTS="value1,value2,value3"
#create regex from it
ARGUMENTS=%$(sed s/,/%|%/g<<<"$ARGUMENTS")%
PSQL_COMMAND="SELECT id FROM users;"
# get records from DB and collapse the list to one line of paragraph-separated words
OUTPUT=%$(psql -U username -d database_name -c "$PSQL_COMMAND" -t| tr [\012\015] [%%])%
sed -E "s/($ARGUMENTS)/%has rights: 1%n/g;s/%%/%/g;s/: %/: /g;s/%/n/g"<<<"$OUTPUT"

    This code will break as soon as your value list exceeds the line size limit of SED.

    e.g. a statement like

    grep -E "$ARGUMENTS" <<< "$OUTPUT"

    when issued from command line in interactive shell with ESC sequence support would highlight all found values. In script call, there is no colored output of "grep", so it will just find the only existing data line … no use for that of cause. The interactive call of grep is just mentioned to show some of your options.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search