About this question
Im new to the PostgreSQL world so I do apologise in advance for not knowing if there is an obvious answer to these questions.
Basically I'm looking for two things. First a simple "contains" based search where the python equivalent would be something like:
def find_all_containing( input_array=[], target ):
output=[]
for i in input_array:
if target in i:
output.append(i)
return(output)
In the database that I'm currently building, logically, I'm inclined to believe that the SQL command would look a bit like this if I were trying to show all of the snippet titles that contained 'evil' in the description.
SELECT title FROM snippets WHERE 'evil' in description;
/* or */
SELECT title FROM snippets WHERE description CONTAINS 'evil';
I wrote an inline python function that returns a 'TRUE' or 'FALSE' to make the following work.
/* contains function here */
CREATE FUNCTION contains (input_text text, target text) RETURNS text AS $$
if target in input_text:
return("TRUE")
return("FALSE")
$$ LANGUAGE plpythonu;
/* working command of the script */
SELECT title FROM snippets WHERE contains(description,'Evil')='TRUE';
This works and should stop me from asking, but I would really prefer to learn how the SQL world would solve this because the whole point of learning SQL was to learn how to work the database instead of just finding another way to mould python to recreate a nosql feel.
The second thing is, this isn't the first time I've thrown together some python snippets to bring more of a pythonic mentality to the database. For those who are well dug into the SQL world am I missing out on something by doing this? I get it for speed but if speed is ever an issue, I would assume that I just need to rewrite the implementation in C.
Keeping it as simple as I can has worked out pretty well so far and this works as is for now. But, when I find myself embedding another language into the code for nearly every solution, that's when I start asking if I'm missing a bigger picture.
