On 10/19/2017 12:14 PM, Tom Lane wrote:
"Igal @ Lucee.org" <igal@xxxxxxxxx> 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!
|
