Обсуждение: Parameter for query
Is there any way to set a variable or parameter for a query? I have a long query where a certain variable needs to be easy to change. I'd like to do something like: threshold = 10.3 SELECT... WHERE x > $threshold... AND y * 1.3 > $threshold... Currently, I need to do this in a scripting language. Is there a straightforward way to do this directly in Postgres?
On Wed, Jul 17, 2013 at 1:25 PM, Robert James <srobertjames@gmail.com> wrote: > Is there any way to set a variable or parameter for a query? > > I have a long query where a certain variable needs to be easy to > change. I'd like to do something like: > > threshold = 10.3 > > SELECT... WHERE x > $threshold... AND y * 1.3 > $threshold... > > Currently, I need to do this in a scripting language. Is there a > straightforward way to do this directly in Postgres? Using which client? psql? In this case there is that: http://www.postgresql.org/docs/devel/static/app-psql.html#APP-PSQL-INTERPOLATION postgres=# \set foo 1 postgres=# select :foo; ?column? ---------- 1 (1 row) mpaquier=# \set foo bar mpaquier=# select :'foo'; ?column? ---------- bar (1 row) Regards, -- Michael
On Wed, Jul 17, 2013 at 6:25 AM, Robert James <srobertjames@gmail.com> wrote: > Is there any way to set a variable or parameter for a query? > > I have a long query where a certain variable needs to be easy to > change. I'd like to do something like: > > threshold = 10.3 > > SELECT... WHERE x > $threshold... AND y * 1.3 > $threshold... > Using prepared statements? See here: http://www.postgresql.org/docs/9.2/static/sql-prepare.html > Currently, I need to do this in a scripting language. Is there a > straightforward way to do this directly in Postgres? well, if you are doing this in a script(ing) it should be quite easy to do string interpolation. Luca