Tell me more ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 6 down vote favorite
1

I want to perform a search in several columns of a table. I use the following query:

select *
from Tabela t
inner join "TabelaPai" tp on tp."ID" = t."RefTabelaPai" and tp."RefProject" = 'projectid'
where not t."Deleted" 
 and (t.Col1 ~ '.*__param1__.*' or t.Col2 ~ '.*__param1__.*' or t.Col3 ~ '.*__param1__.*'
   or t.Col4 ~ '.*__param1__.*' or t.Col5 ~ '.*__param1__.*' or t.Col6 ~ '.*__param1__.*' 
   or t.Col7 ~ '.*__param1__.*' or t.Col8 ~ '.*__param1__.*' or t.Col9 ~ '.*__param1__.*');

This will search for the keyword __param1__ in any of the columns and it's working fine.

But I don't like the way the query looks like. Any suggestion on how to refactor the query so it can look 'prettier' (without those ~ '.*__param1__.*' repetitions, for example)?

Edit: A little of context about the query:

What leads to this usage is that I can parameterize the data in the table. For example, I have a column in a table where scripts are saved. My application allows the users to parametrize the script using something like __param1__. If the user wants to rename the parameter I'll have to search for the usage of the parameter in every column that is parameterizable, and this is the query that finds where the parameter is used.

share|improve this question
2  
I think this is more of a database design issue rather than a query issue. How is your database used such that you need to search for the same value across several columns to find something? What's your database schema? – user4549 May 25 '11 at 20:09
I think your kind of right.. – jpsstavares May 26 '11 at 8:18
Question updated with context. – jpsstavares May 26 '11 at 8:24
With such a task maybe you should keep variable name separately from its usage. Instead of storing script with variable name you can store it with some placeholder like {param1}. This will allow renaming parameters easily but you will have to replace placeholders in return. – Snowbear May 26 '11 at 8:59
The placehoders are the __ in the beggining and in the end. The user interacts with the name param1. – jpsstavares May 26 '11 at 12:29

2 Answers

up vote 3 down vote accepted

I must admit, I don't really see what's wrong with the repetition — assuming it is what you're wanting to do (and your columns aren't actually named t.Colx!). If I came across this query in a project, I'd know pretty quickly what it's doing I think: searching a bunch of columns for a single supplied value (e.g. searching name, address, phone, etc. with a single search box, perhaps).

As for the matter of storing scripts and their parameters in a database: I'd probably go for a second key-value table, something like:

scripts { id, name, body }
script_parameters { id, script_id, name, value }

And you'd fetch the script and parameters and substitute the latter into the former in the app.

But then, I'm probably quite missing the point of what you're trying to do! :-)

share|improve this answer
Well, maybe I'm just being picky... When I see an example like this with so many repetitions I think immediately that it can be generalized. But as you said maybe there is nothing wrong with this query and just have to let it go :) – jpsstavares May 27 '11 at 8:15
up vote 2 down vote

Something "prettier"? And so many repetitions call for generalization?
There is a way:

SELECT *
FROM   tabela t
JOIN   "TabelaPai" tp ON tp."ID" = t."RefTabelaPai"
WHERE  NOT t."Deleted" 
AND    tp."RefProject" = 'projectid'
AND    t::text ~~ E'%\\_\\_param1\\_\\_%'

Major points:

  • You can reference the composite type of every relation in the SELECT list. I quote the manual here:

Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type.

You can cast the whole composite type to its text representation in one fell swoop, which is a syntactical shorthand for concatenating all columns in text-form with an additional COALESCE() to filter NULL values. The resulting filter in my query is guaranteed to find every occurrence in the whole row.

  • The LIKE operator (~~) is faster than regular expression pattern matching (~) most of the time. Regular expressions are far more powerful, but whenever LIKE can do the job use it. Its syntax is simpler, too.

  • Edit: underscores (_) have a special meaning for the LIKE operator, so you need to escape them if you want to match a literal _. Default escape character is \, which also has a special meaning in escape string constants, so you have to double them and use the E'' prefix: E'\\_'.
    As of version 9.1, the default for the setting standard_conforming_strings is on. In this case \ has no special meaning in strings and you don't need to double them any more.

Some minor cleanup / simplification:

  • JOIN is shorthand for INNER JOIN.
  • It is cleaner to write a AND tp."RefProject" = 'projectid' as WHERE clause, as it has no connection to tabela. Else you could put NOT t."Deleted" into the JOIN condition as well. Either way, you get the same result here.
  • No point for mixed case in Tabela - it is folded to tabela anyway.
  • A word of advise: if you abstain from using mixed case identifiers, you will never have to double-quote them and avoid possible confusion. By default (and without double-quotes), all identifiers are folded to lower case automatically.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.