"Igal @ Lucee.org" <igal@xxxxxxxxx> writes:
> On 10/19/2017 8:44 AM, David G. Johnston wrote:
>> Adding lots of new custom syntax to pure server-side parsed SQL is a
>> non-trivial undertaking whose need is reduced by the alternatives so
>> described (functions, DO block, PREPARE, psql).
> I still think that using server side variable is a much easier and
> intuitive way of doing this. All of the alternatives have major flaws.
Server-side variables aren't exactly flaw-free either. We looked into
that before and couldn't really come to agreement on a good definition.
You might want to check the archives. However, I'd just point out that
variables, per se, aren't that useful unless you also have control flow
constructs, and at that point you're way outside SQL.
> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
