Re: Using Variables in Queries

2017-10-19 20:11 GMT+02:00 Igal @ Lucee.org <igal(at)lucee(dot)org>:

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

the dynamic like MySQL miss data type info, the dynamic like MSSQL is good
for interactive work and bad for stored procedures (it breaks a possibility
to do static check of SQL commands), and static solution like DB2 is great
for stored procedures and difficult for interactive work - the variables
should be dropped. Another issue is using SET command for different use
case in Postgres. And second issue is using variables from other than
PLpgSQL and SQL environments (PLPythonu, PLPerl).

If you like this feature, then is necessary for your work, but only two
(three) databases on the world has similar feature Sybase, MSSQL and MySQL.
Usually the people has little bit different style of work optimized for any
database, and unfortunately this is not portable.

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