title: How to select data using table/column names stored in a table? Dynamically building SQL?
body: <p>I have a table that shows events and vehicles associated with those events:</p>
  
  <pre><code>id | model | colour     | cargo
  --------------------------------------------
  1 | vw       | red      | tomatoes
  2 | bmw      | red      | golf clubs
  </code></pre>
  
  <p>I would like to have a table that I can use to manage alerts. There is a column that represents strings to check for, another column that shows which column/table it is applied to and some text that describes the alert:</p>
  
  <pre><code>id | column_name    | check_string     | alert_string
  --------------------------------------------
  1 | my_table.colour | red              | 'Vehicle is red'
  2 | my_table.cargo  | [sport,club]     | 'Vehicle is carrying sports goods'
  </code></pre>
  
  <p>or possibly:</p>
  
  <pre><code>id | column_name    | check_string     | alert_string
  --------------------------------------------
  1 | my_table.colour | red              | 'Vehicle is red'
  2 | my_table.cargo  | sport            | 'Vehicle is carrying sports goods'
  3 | my_table.cargo  | club             | 'Vehicle is carrying sports goods'
  </code></pre>
  
  <p>I would like to have a query that I can run that would return all the alerts that apply to that row:</p>
  
  <pre><code>id | alert_text
  --------------------------------------------
  1 | ['Vehicle is red']
  2 | ['Vehicle is red', 'Vehicle is carrying sports goods']
  </code></pre>
  
  <p>The way I was doing this was building up SQL commands in Python and running them against the database but this becomes a burden as the number of rules grow or variables need to be changed/updated (suddenly we don't care about red cars but are very concerned with blue cars). </p>
  
  <pre><code>SELECT id, 'Vehicle is red' as alert_text FROM my_table 
  WHERE my_table.colour = 'red';
  
  SELECT id, 'Sports goods' as alert_text FROM my_table 
  WHERE my_table.cargo in ['sport', 'club'];
  
  SELECT &lt;many other rules&gt;;
  </code></pre>
  
  <p>Is there a better way to do this? Is it worth building a DB table that can dynamically point to a column, strings to check in that column and then alert text associated with the rule? Should I even be using SQL for this problem?</p>
  
  <p>I've got a feeling that SQL is maybe not the right tool for this job but I don't know what I don't know...</p>