Using WHERE with multiple columns with different data types to satisfy a single input in bash and po-CodePudding

please assist with the following. i m trying to run a script that accepts one argument $1. The argument can either be a string or character or an integer. I want to use the argument in there where clause to search for the element in the database.

This is the table i want to search from:enter image description here

When i use the multiple conditions with OR , it works only when either the argument is a number or text. This what my code looks like enter image description here `

ELEMENT=$($PSQL "SELECT * FROM elements e FULL JOIN properties p USING(atomic_number) WHERE symbol = '$1' OR name = '$1' OR atomic_number = $1;")

and this is the results i get when i run with different aurgumentsenter image description here

Please help.

Thank you in advance

CodePudding user response：

This will always fail on any non-numeric argument.

You are passing in H for hydrogen, but taking whatever was passed in and using it in the atomic_number comparison as an unquoted number, which the DB engine is trying to figure out what to do with. H isn't a number, and isn't a quoted string, so it must be the name of a column...but it isn't, so you are using invalid syntax.

I don't have a postgres available right now, but try something like this -

ELEMENT=$( $PSQL "
      SELECT * 
        FROM elements e 
   FULL JOIN properties p USING(atomic_number) 
       WHERE symbol = '$1' 
          OR name = '$1' 
          OR atomic_number = CAST(`$1` as INTEGER); " )

Also, as an aside... avoid all-capital variable names.
As a convention, those are supposed to be system vars.

And please - please don't embed images except as helpful clarification.
Never rely on them to provide info if it can be avoided. Copy/paste actual formatted text people can copy/paste in their own testing.

CodePudding user response：

An alternate way to construct the query: requires bash

looks_like_a_number() {
    # only contains digits
    [[ "$1" ==  ([[:digit:]]) ]]
}

sanitize() {
    # at a minimum, handle embedded single quotes
    printf '%s' "${1//\'/\'\'}"
}

if looks_like_a_number "$1"; then
    field="atomic_number"
    value=$1
elif [[ ${#1} -eq 1 ]]; then
    field="symbol"
    printf -v value "'%s'" "$(sanitize "$1")"
else
    field="name"
    printf -v value "'%s'" "$(sanitize "$1")"
fi

q="SELECT *
   FROM elements e
   FULL JOIN properties p USING(atomic_number)
   WHERE $field = $value;"

printf '%s\n' "$q"

result=$("$PSQL" "$q")