Re: Using Variables in Queries

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