I am creating a search tool for Postgres. It will have several text entries (one for each table column). When the text entry is blank, I want the query to match each and every table entry. Is this possible? I have tried:
SELECT name FROM contacts WHERE surname = '*';
SELECT name FROM contacts WHERE surname = *;
As you can imagine, this returns nothing since there is no surname of *, and the second query is invalid. Any ideas?
EDIT:
Because of the nature of what I am doing, a SELECT name FROM contacts is not sufficient. I guess I could make it work, but it would be ugly. I want a WHERE for every column in the table, but if the search for any given column is an empty string, I want it to fetch every entry in the table.
I want the query to match each and every table entry
Just use SELECT without WHERE surname clause. Simple as that.
SELECT name FROM contacts
But if you want to use one query, you can do it this way:
cur.execute(
"""SELECT name FROM contacts
WHERE (CASE WHEN %(entry)s != '' THEN surname = %(entry)s ELSE true END)"""
, {'entry': "John Doe"}
)
As for your answer, you used regular expressions matching. In the question you never mentioned it. So I assume it's not what you wanted. Regular expressions have specific syntax, and if some invalid regex format is entered into the text entry, the query will fail.