Thread: [GENERAL] Using Variables in Queries
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.
Thanks,
Igal Sapir
Lucee Core Developer
Lucee.org
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 @queryThe 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.
--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
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 @queryThe 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
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;This works, but requires `DEALLOCATE sqlquery` when you want to update it from what I've seen which is not very friendly.
EXECUTE sqlquery('red widget');
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:
I'm not sure what is the difference between static and dynamic in this context?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.
My real query is for similarity here, so I'm testing different functions with the same value, e.g.p.s. Your query should be terribly slow. When I see it, I am less sure, so server side variables are good idea :)
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
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;This works, but requires `DEALLOCATE sqlquery` when you want to update it from what I've seen which is not very friendly.
EXECUTE sqlquery('red widget');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 setAs 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:I'm not sure what is the difference between static and dynamic in this context?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.
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.
My real query is for similarity here, so I'm testing different functions with the same value, e.g.p.s. Your query should be terribly slow. When I see it, I am less sure, so server side variables are good idea :)
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 @ 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
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:
This is beautiful, thank you!
"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