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