Postgres format string using array

Question

I'm looking for an easy way to format a string using an array, like so:

select format_using_array('Hello %s and %s', ARRAY['Jane', 'Joe']);

 format_using_array
--------------------
Hello Jane and Joe
(1 row)

There's a format function but it needs explicit arguments and I don't know how many items are there in the array. I came up with a function like that:

CREATE FUNCTION format_using_array(fmt text, arr anyarray) RETURNS text
    LANGUAGE plpgsql
AS $$
    declare 
        t text;
        length integer;
    begin
        length := array_length(arr, 1);
        t := fmt;
        for i in 1..length loop
           t := regexp_replace(t, '%s', arr[i]);
        end loop;

        return t;
    end
$$;

But maybe there's an easier way that I don't know of, it's my first day using pgsql.

Answer 1

You can use a format function and VARIADIC keyword. It requires 9.3, where is fixed bug in variadic function implementation

postgres=# SELECT format('%s %s', 'first', 'second');
    format    
--------------
 first second
(1 row)

postgres=# SELECT format('%s %s', ARRAY['first', 'second']);
ERROR:  too few arguments for format
postgres=# SELECT format('%s %s', VARIADIC ARRAY['first', 'second']);
    format    
--------------
 first second
(1 row)

Answer 2

In case you missed it, Postgres comes with a built-in function that basically wraps C's sprintf, that takes any number of arguments, will be faster, and is less verbose than what you're trying to create:

select format('Hello %s and %s', 'Jane', 'Joe');       -- Hello Jane and Joe

It'll also be less bug prone at that, considering that it allows positional arguments rather than relying on regexp_replace(), and supports formatting flags as a bonus:

select format('Hello %2$s and %1$s', 'Jane', 'Joe');   -- Hello Joe and Jane

http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-FORMAT

At any rate, if you truly persist at wanting such an array-driven function, you'll probably need to unnest() the array in order to build (and properly escape) an SQL string, so as to ultimately call the above-mentionned format() using dynamic SQL:

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN