Can I use the same variable in different positions in dynamic SQL query in PostgreSQL-CodePudding

I am using PostgreSQL to create complexe dynamic queries. In my queries I can use one variable multiple times in multiple positions in the query, to simplify things, I want to know if I can do something like this :

SELECT * FROM employees Where name = $1 and id = $2 and manager_id = $2;

And then execute the query like this :

EXECUTE format ('SELECT * FROM employees Where name = $1 and id = $2 and manager_id = $2;') using (var_name, var_id);

CodePudding user response：

The parameters are positional, so $1 always refers to the first parameter regardless where and how often you refer to it.

But you must not enclose the parameters in parentheses because that creates an anonymous record. using (var_name, var_id) passes a single parameter (which is a record with two fields).

So you can use:

EXECUTE format('SELECT * FROM employees Where name = $1 and id = $2 and manager_id = $2') 
 using var_name, var_id;