On 10/19/2017 12:14 PM, Tom Lane wrote:
"Igal @ Lucee.org" <i...@lucee.org> writes:
My real query is for similarity here, so I'm testing different functions
with the same value, e.g.
SELECT item_name
, similarity('red widget', item_name)
, similarity(item_name, 'red widget')
, word_similarity('red widget', item_name)
, word_similarity(item_name, 'red widget')
, item_name <->> 'red widget'
, item_name <<-> 'red widget'
, 'red widget' <<-> item_name
FROM products
WHERE similarity('red widget', item_name) > 0.25
ORDER BY 'red widget' <<-> item_name
So each time I want to change the phrase it's a slower process than what
I'm used to (think multiple-cursor in modern text editors, or a
server-side variable)
Well, this is simply not exploiting SQL very well. You could use a
VALUES subquery to provide the string you're using elsewhere in the query.
SELECT item_name
, similarity(target, item_name)
, similarity(item_name, target)
, word_similarity(target, item_name)
, word_similarity(item_name, target)
, item_name <->> target
, item_name <<-> target
, target <<-> item_name
FROM products,
(values ('red widget'::text)) consts(target)
WHERE similarity(target, item_name) > 0.25
ORDER BY target <<-> item_name
PG 9.5 and up will flatten out cases like this to be exactly what you
wrote out longhand.
regards, tom lane
This is beautiful, thank you!
Igal Sapir
Lucee Core Developer
Lucee.org <http://lucee.org/>
