title: Using prepared statement in stored function
body: <p>I have a table in the database:</p>
  
  <pre><code>create table store (
      ...
      n_status        integer not null,
      t_tag           varchar(4)
      t_name          varchar,
      t_description   varchar,
      dt_modified     timestamp not null,
      ...
  );
  </code></pre>
  
  <p>In my stored function I need to execute the same <code>select</code> against this table multiple times:</p>
  
  <pre><code>select * from store
  where n_place_id = [different values]
  and t_tag is not null
  and n_status &gt; 0
  and (t_name ~* t_search or t_description ~* t_search)
  order by dt_modified desc
  limit n_max;
  </code></pre>
  
  <p>Here, <code>t_search</code> and <code>n_max</code> are parameters into the stored function.  I thought it would make sense to use a prepared statement for this, but I'm running into strange problems.  Here's what I have:</p>
  
  <pre><code>create or replace function fn_get_data(t_search varchar, n_max integer)
    returns setof store as
  $body$
  declare
      resulter        store%rowtype;
      mid             integer;
  begin
      prepare statement prep_stmt(integer) as
          select *
            from store
           where n_place_id = $1
             and (t_name ~* t_search or t_description ~* t_search)
        order by dt_modified
           limit n_max;
  
      for mid in
          (select n_place_id from ... where ...)
      loop
          for resulter in
              execute prep_stmt(mid)
          loop
              return next resulter;
          end loop;
      end loop;
  end;$body$
    language 'plpgsql' volatile;
  </code></pre>
  
  <p>However when I actually run the function with</p>
  
  <pre><code>select * from fn_get_data('', 30)
  </code></pre>
  
  <p>I receive this error:</p>
  
  <pre><code>ERROR:  column "t_search" does not exist
  LINE 3:   and (t_name ~* t_search or t_description ~* t_search)
                           ^
  QUERY:  prepare prep_stmt(integer) as
          select * from store where n_status &gt; 0 and t_tag is not null and n_museum = $1
          and (t_name ~* t_search or t_description ~* t_search)
          order by dt_modified desc limit maxres_free
  </code></pre>
  
  <p>Ok, maybe it doesn't like external variables in the prepared statement, so I changed this to be</p>
  
  <pre><code>prepare prep_stmt(integer, varchar, integer) as
  select * from store where n_status &gt; 0 and t_tag is not null and n_museum = $1
  and (t_name ~* $2 or t_description ~* $2)
  order by dt_modified desc limit $3
  
  ...
  
  for resulter in
      execute prep_stmt(mid, t_search, n_max)
  
  ...
  </code></pre>
  
  <p>This time I get a different error:</p>
  
  <pre><code>ERROR:  function prep_stmt(integer, character varying, integer) does not exist
  LINE 1: SELECT prep_stmt(mid, t_search, n_max)
                 ^
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
  QUERY:  SELECT prep_stmt(mid, t_search, n_max)
  </code></pre>
  
  <p>What am I missing here?</p>
  
  <p><strong>EDIT</strong> I added the relevant table structure at the top.</p>