0

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);
Rym
  • 5
  • 1

1 Answers1

0

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;
  • Thank you for your answer @a_horse_with_no_name, this is just an example to explain the issue but I have more complexe queries where I need to use dynamic sql. Do you please know if inversing $1 and $2 still works correctly and $1 still references the first parameter even if it is not in the first position ? – Rym May 16 '22 at 07:13
  • Yes the numbers are positional. But as written your query only passes a **single** parameter. Do not enclose the list of parameters with parentheses. –  May 16 '22 at 07:21