Re: [GENERAL] Using Variables in Queries

2017-10-19 Thread Igal @ Lucee.org
On 10/19/2017 12:14 PM, Tom Lane wrote: "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')

Re: [GENERAL] Using Variables in Queries

2017-10-19 Thread Tom Lane
"David G. Johnston" writes: > On Thu, Oct 19, 2017 at 12:14 PM, Tom Lane 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

Re: [GENERAL] Using Variables in Queries

2017-10-19 Thread David G. Johnston
On Thu, Oct 19, 2017 at 12:14 PM, Tom Lane 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 >

Re: [GENERAL] Using Variables in Queries

2017-10-19 Thread Tom Lane
"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

Re: [GENERAL] Using Variables in Queries

2017-10-19 Thread Pavel Stehule
2017-10-19 20:11 GMT+02:00 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

Re: [GENERAL] Using Variables in Queries

2017-10-19 Thread 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.  

Re: [GENERAL] Using Variables in Queries

2017-10-19 Thread David G. Johnston
On Thu, Oct 19, 2017 at 8:21 AM, 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

Re: [GENERAL] Using Variables in Queries

2017-10-19 Thread Pavel Stehule
Hi 2017-10-19 17:21 GMT+02:00 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

Re: [GENERAL] Using Variables in Queries

2017-10-19 Thread Alban Hertroys
On 19 October 2017 at 17:25, Scott Mead wrote: > > > On Thu, Oct 19, 2017 at 11:21 AM, 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

Re: [GENERAL] Using Variables in Queries

2017-10-19 Thread Scott Mead
On Thu, Oct 19, 2017 at 11:21 AM, 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

[GENERAL] Using Variables in Queries

2017-10-19 Thread 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