Using Variables in Queries

On Thu, Oct 19, 2017 at 11:21 AM, Igal @ Lucee.org <igal@lucee.org> wrote:

Hello,

In other database servers, which I'm finally dropping in favor of
Postgres, I can do the following (mind you that this is for illustration
only, I do not actually write queries like that):

DECLARE @query varchar(64) = 'red widget';

SELECT *
FROM products
WHERE col1 LIKE @query
OR col2 LIKE @query
OR col3 LIKE @query
OR col4 LIKE @query
OR col5 LIKE @query

The point is, though, that I can change the @query variable in one place
which is very convenient.

Is it still true (the posts I see on this subject are quite old) that I
can not do so in Postgres outside of a stored procedure/function?

You can do this if you're using psql. This doesn't work if you're using
something like JDBC, etc..., but from psql it works great.

https://www.postgresql.org/docs/9.5/static/app-psql.html#APP-PSQL-VARIABLES

--Scott

And if so, what's the reason of not adding this feature? Seems very
useful to me.

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org <http://lucee.org/&gt;

--
--
Scott Mead
Sr. Architect
*OpenSCG <http://openscg.com&gt;*
http://openscg.com

On 19 October 2017 at 17:25, Scott Mead <scottm@openscg.com> wrote:

On Thu, Oct 19, 2017 at 11:21 AM, Igal @ Lucee.org <igal@lucee.org> wrote:

Hello,

In other database servers, which I'm finally dropping in favor of
Postgres, I can do the following (mind you that this is for illustration
only, I do not actually write queries like that):

DECLARE @query varchar(64) = 'red widget';

SELECT *
FROM products
WHERE col1 LIKE @query
OR col2 LIKE @query
OR col3 LIKE @query
OR col4 LIKE @query
OR col5 LIKE @query

The point is, though, that I can change the @query variable in one place
which is very convenient.

Is it still true (the posts I see on this subject are quite old) that I
can not do so in Postgres outside of a stored procedure/function?

You should be able to do that using the DO statement:
https://www.postgresql.org/docs/9.6/static/sql-do.html

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi

2017-10-19 17:21 GMT+02:00 Igal @ Lucee.org <igal@lucee.org>:

Hello,

In other database servers, which I'm finally dropping in favor of
Postgres, I can do the following (mind you that this is for illustration
only, I do not actually write queries like that):

DECLARE @query varchar(64) = 'red widget';

SELECT *
FROM products
WHERE col1 LIKE @query
OR col2 LIKE @query
OR col3 LIKE @query
OR col4 LIKE @query
OR col5 LIKE @query

The point is, though, that I can change the @query variable in one place
which is very convenient.

Is it still true (the posts I see on this subject are quite old) that I
can not do so in Postgres outside of a stored procedure/function? And if
so, what's the reason of not adding this feature? Seems very useful to me.

Currently psql has client side variables. pgAdmin3 has some script language
too - https://www.pgadmin.org/docs/pgadmin3/1.22/pgscript.html Server side
variables are accessible only inside PLpgSQL variables.

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.

p.s. Your query should be terribly slow. When I see it, I am less sure, so
server side variables are good idea :)

Regards

Pavel

On Thu, Oct 19, 2017 at 8:21 AM, Igal @ Lucee.org <igal@lucee.org> wrote:

Is it still true (the posts I see on this subject are quite old) that I
can not do so in Postgres outside of a stored procedure/function? And if
so, what's the reason of not adding this feature? Seems very useful to me.

​<not positive on syntax but the concept should be sound>​

​PREPARE sqlquery AS​ SELECT * FROM products WHERE col1 LIKE $1 OR col2
LIKE $1;
EXECUTE sqlquery('red widget');

Alban's DO blocks are problematic since they are incapable of generating a
result set.

As Scott said people needing this functionality in PostgreSQL are content
with using psql.

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

David J.

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/&gt;

2017-10-19 20:11 GMT+02:00 Igal @ Lucee.org <igal@lucee.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.

"Igal @ Lucee.org" <igal@lucee.org> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Oct 19, 2017 at 12:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

​Does it matter if the values expression is embedded in a CTE?​ I find the
construct:

WITH constants AS ( VALUES (,,) )
SELECT * FROM tbl CROSS JOIN constants

nice as it keeps the constants at the top of the query string. I presume
pure style differences between using multiple from items and an explicit
cross join.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thu, Oct 19, 2017 at 12:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

​Does it matter if the values expression is embedded in a CTE?

Yes, CTEs are optimization fences ...

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/19/2017 12:14 PM, Tom Lane wrote:

"Igal @ Lucee.org" <igal@lucee.org> writes:

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

This is beautiful, thank you!

Igal Sapir
Lucee Core Developer
Lucee.org <http://lucee.org/&gt;