Re: [GENERAL] Using Variables in Queries

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!