Re: Using Variables in Queries

"Igal @ Lucee.org" <igal@xxxxxxxxx> · Thu, 19 Oct 2017 11:11:44 -0700



    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