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