title: What are the rules governing how a bind variable can be used in Postgres and where is this defined?
body: <p>I can have a table and function defined as:</p>
  
  <pre><code>CREATE TABLE mytable
  (
    mycol integer
  );
  
  INSERT INTO mytable VALUES (1);
  
  CREATE OR REPLACE FUNCTION myfunction (l_myvar integer) RETURNS mytable AS $$
  
  DECLARE
  l_myrow mytable;
  
  BEGIN
  
  SELECT      *
  INTO        l_myrow
  FROM        mytable
  WHERE       mycol = l_myvar;
  
  RETURN l_myrow;
  
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>In this case l_myvar acts as a bind variable for the value passed when I call:</p>
  
  <pre><code>SELECT * FROM myfunction(1);
  </code></pre>
  
  <p>and returns the row where mycol = 1</p>
  
  <pre><code>If I redefine the function as:
  
  CREATE OR REPLACE FUNCTION myfunction (l_myvar integer) RETURNS mytable AS $$
  
  DECLARE
  l_myrow mytable;
  
  BEGIN
  
  SELECT      *
  INTO        l_myrow
  FROM        mytable
  WHERE       mycol IN (l_myvar);
  
  RETURN l_myrow;
  
  END;
  $$ LANGUAGE plpgsql;
  
  SELECT * FROM myfunction(1);
  </code></pre>
  
  <p>still returns the row where mycol = 1</p>
  
  <p>However, if I now change the function definition to allow me to pass an integer array and try to this array in the IN clause, I get an error:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION myfunction (l_myvar integer[]) RETURNS mytable AS $$
  
  DECLARE
  l_myrow mytable;
  
  BEGIN
  
  SELECT      *
  INTO        l_myrow
  FROM        mytable
  WHERE       mycol IN (array_to_string(l_myvar, ','));
  
  RETURN l_myrow;
  
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>Analysis reveals that although:</p>
  
  <pre><code>SELECT array_to_string(ARRAY[1, 2], ',');
  </code></pre>
  
  <p>returns 1,2 as expected</p>
  
  <pre><code>SELECT * FROM myfunction(ARRAY[1, 2]);
  </code></pre>
  
  <p>returns the error</p>
  
  <pre><code>operator does not exist: integer = text
  </code></pre>
  
  <p>at the line:</p>
  
  <pre><code>WHERE       mycol IN (array_to_string(l_myvar, ','));
  </code></pre>
  
  <p>If I execute:</p>
  
  <pre><code>SELECT      *
  FROM        mytable
  WHERE       mycol IN (1,2);
  </code></pre>
  
  <p>I get the expected result.</p>
  
  <p>Given that array_to_string(l_myvar, ',') evaluates to 1,2 as shown, why arent these statements equivalent.</p>
  
  <p>From the error message it is something to do with datatypes, but doesnt the IN(variable) construct appear to be behaving differently from the = variable construct?</p>
  
  <p>What are the rules here?</p>
  
  <p>I know that I could build a statement to EXECUTE, treating everything as a string, to achieve what I want to do, so I am not looking for that as a solution. I do want to understand though what is going on in this example. Is there a modification to this approach to make it work, the particular example being to pass in an array of values to build a dynamic IN clause without resorting to EXECUTE?</p>
  
  <p>Thanks in advance</p>
  
  <p>Craig</p>