title: Placeholder templates with PostgreSQL
body: <p>I need a function like</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT vformat('Hello %%! Bye %%...', array['John','Maria'], '%%');
  -- 'Hello John! Bye Maria...'
  </code></pre>
  
  <p>Supplying a string with placeholders (a template), and corresponding inputs into a vector, it returns a text. The "placeholder mark" is a free string.</p>
  
  <p>I can't use the pg9.1 format (like sprintf) function, because it does not allow other mark than '%' (see ex. <a href="http://docs.python.org/library/string.html#formatstrings" rel="nofollow">Python formatter</a>) and not allow array parameter (see ex. <a href="http://www.php.net/manual/en/function.vsprintf.php" rel="nofollow">vsprintf</a>). 
  The SIMPLEST solution with PostgreSQL 9.0 (I think 8.4+) is  </p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE FUNCTION array_zipper_string(anyarray,anyarray) RETURNS text AS $$ 
    -- use with bigger array at left
    SELECT string_agg(t.val||coalesce($2[t.idx],''),'')  
    FROM  (SELECT generate_subscripts($1, 1) AS idx, unnest($1) AS val) AS t;
  $$ LANGUAGE SQL IMMUTABLE;
  
  CREATE FUNCTION tpl_positional(text,anyarray,varchar DEFAULT '%%') 
  RETURNS text AS $$ 
    SELECT array_zipper_string(string_to_array($1,$3),$2);
  $$ LANGUAGE SQL IMMUTABLE;
  CREATE FUNCTION tpl_positional(
     text, text, varchar DEFAULT '%%',varchar DEFAULT '|' 
  ) RETURNS text AS $$ 
    SELECT tpl_positional($1,string_to_array($2,$4),$3);
  $$ LANGUAGE SQL IMMUTABLE;
  
  -- TESTING:
  SELECT tpl_positional('Hello %%! Bye %%...', 'John|Maria');
  SELECT tpl_positional('Hello %%!',array['John']);
  </code></pre>
  
  <p>There are  well-knowed (open source) <em>function</em> or <em>library</em> for do the same? </p>
  
  <p>PS: that do this and also something more (!) ... My wishes list:</p>
  
  <ul>
  <li>open source from a standard library </li>
  <li>option to indexable placeholders, 'Hello %%{1}! Bye %%{2}...'</li>
  <li>option to use formatters, 'There are %%2d monkeys.' or 'There are %%{1|2d} monkeys.';    </li>
  </ul>