title: Nested query as PostGIS function parameter
body: <p>I have a PostGIS query where I really need to have nested queries inside PostGIS function calls:</p>
  
  <pre><code>UPDATE raw.geocoding
  SET the_geom = ST_Centroid(
     ST_Collect(
       SELECT the_geom
       FROM raw.geocoding
       WHERE hash = ((E'0101000020090C000081610F9CC5DC3341EE672E6E723B3241')::varchar),
       SELECT the_geom
       FROM raw.geocoding
       WHERE hash = ((E'0101000020090C00002CF887E0C5DC3341C9E5B2DF2A383241')::varchar)
      )
    )
  WHERE hash = ((E'3e638a27c6c38f05026252f4a0b57b2e')::varchar)
  </code></pre>
  
  <p>Unfortunately, this doesn't work. I get a syntax error at the beginning of the nested query:</p>
  
  <pre><code>ERROR:  syntax error at or near "SELECT"
  LINE 4:          SELECT the_geom
                   ^
  
  ********** Error **********
  
  ERROR: syntax error at or near "SELECT"
  SQL state: 42601
  Character: 86
  </code></pre>
  
  <p>Looks like I cannot have a nested query as a PostGIS function parameter?</p>
  
  <p>I've perused through the PostGIS documentation and cannot find any clear guidance for dealing with this.</p>
  
  <p>It appears Postgres has a way of doing variables in pgSQL, but it's unclear to me how this would be pulled off in a standard query. This is a query that will be run tens or hundreds of thousands of times from a C# program. That aside, I could do a pgSQL stored procedure if required; just wanted to make sure there wasn't a simpler alternative first.</p>
  
  <p>In case you were wondering, the query looks messy because it's the result of a npgsql-generated parameterized query. I think it's fair to say that npgsql is being extra-cautious with redundant typing and escaping.</p>
  
  <p>I am running PostGIS 2.0.1, Postgres 9.1.5, and npgsql 2.0.12.</p>