Re: Using Variables in Queries

On 10/19/2017 8:44 AM, David G. Johnston wrote:

> ​PREPARE sqlquery AS​ SELECT * FROM products WHERE col1 LIKE $1 OR
> col2 LIKE $1;
>  EXECUTE sqlquery('red widget');
This works, but requires `DEALLOCATE sqlquery` when you want to update
it from what I've seen which is not very friendly.

>  Alban's DO blocks are problematic since they are incapable of
> generating a result set.
Then that's a no-go for me because I definitely want a result set

>  As Scott said people needing this functionality in PostgreSQL are
> content with using psql.
psql is very nice, but in the age of GUI it lacks a lot of stuff. I am
actually using DBeaver which I find to be an excellent  tool that works
with any DBMS and can generate export/import scripts from migrating from
one DMBS to another, which is what I'm after ATM.

>  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.

On 10/19/2017 8:40 AM, Pavel Stehule wrote:
> There was lot of discussion about server side variables
> https://wiki.postgresql.org/wiki/Variable_Design, but nobody write
> final patch. There is not clean result if we want dynamic variables,
> static variables or both.
I'm not sure what is the difference between static and dynamic in this
context?

> p.s. Your query should be terribly slow. When I see it, I am less
> sure, so server side variables are good idea :)
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)

Igal Sapir
Lucee Core Developer
Lucee.org <http://lucee.org/>