Placeholder templates with PostgreSQL

Question

I need a function like

SELECT vformat('Hello %%! Bye %%...', array['John','Maria'], '%%');
-- 'Hello John! Bye Maria...'

Supplying a string with placeholders (a template), and corresponding inputs into a vector, it returns a text. The "placeholder mark" is a free string.

I can't use the pg9.1 format (like sprintf) function, because it does not allow other mark than '%' (see ex. Python formatter) and not allow array parameter (see ex. vsprintf). The SIMPLEST solution with PostgreSQL 9.0 (I think 8.4+) is

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']);

There are well-knowed (open source) function or library for do the same?

PS: that do this and also something more (!) ... My wishes list:

• open source from a standard library
• option to indexable placeholders, 'Hello %%{1}! Bye %%{2}...'
• option to use formatters, 'There are %%2d monkeys.' or 'There are %%{1|2d} monkeys.';

Answer 1

PostgreSQL FORMAT() function formats arguments based on a format string.

If you have worked with C programming language, you will find that the FORMAT() function is similar to the sprintf() function.

SELECT FORMAT('Hello, %s','PostgreSQL');

      format
-------------------
 Hello, PostgreSQL
(1 row)

For details, see http://www.postgresqltutorial.com/postgresql-format/