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.

if you can, check orafce extension https://github.com/orafce/orafce - plvsubst.string is exactly what you want — Pavel Stehule, Dec 20 '13 at 17:06
@PavelStehule seems perfect, but I'm assuming that since this is an extension, there's no built-in function. I'm not sure I'll be able to compile and install an extension in my project. — MarcinJ, Dec 20 '13 at 18:44

score 19 · Accepted Answer · answered Dec 21 '13 at 08:48

19

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)

answered Dec 21 '13 at 08:48

Pavel Stehule

42,331
5
91
94

I wonder if it is possible to make a similar thing where I create the array passed into format in advance. like `variables text[]; FOR col IN SELECT column_name FROM information_schema.columns WHERE table_name = TG_TABLE_NAME LOOP variables := array_append(variables, ('NEW.' || col)); END LOOP; EXECUTE format(insert_query, VARIADIC variables); -- there it is very likely wrong, I don't know how to tell it to use 'variables' as variadic array` – Ondrej Burkert Nov 11 '14 at 11:37
I just found a way for my problem elsewhere (http://stackoverflow.com/questions/7914325/insert-with-dynamic-table-name-in-trigger-function) So I use '($1).col and then using NEW. We don't delete so we don't need to care about NEW/OLD distinction. – Ondrej Burkert Nov 11 '14 at 14:14
Too bad you can't include the format string in the array like `select format(variadic array['It is %s.', 'nice'])`. I have the format string and params in a CSV-style string, e.g. `'It is %s and %s.|nice|warm'` – user9645 Jan 06 '17 at 17:41
@user9645 - you can use array_to_string function - following code is not pretty effective (due repeat parsing) - it is one liner, but it is example, how it can work - `select format((array_to_string(str, '|')[1], variadic (array_to_string(str,'|')[2:]))` - maybe PostgreSQL 9.5 is required due syntax `[x:]` – Pavel Stehule Jan 07 '17 at 05:49
@Pavel - Thanks but that won't work due to error `ERROR: 42804: could not determine polymorphic type because input has type "unknown"` also note your parens are mixed up a bit, I think should be: `select format((array_to_string(str, '|'))[1], variadic (array_to_string(str, '|'))[2:])` otherwise get `ERROR: 42601: syntax error at or near "["` – user9645 Jan 09 '17 at 15:49
1

@user9645: My mistake, sorry - there should be function `string_to_array`: following function is working: `create or replace function fmt(text) returns text as $$ select format((string_to_array($1, '|'))[1], variadic (string_to_array($1, '|'))[2:]) $$ language sql;` – Pavel Stehule Jan 09 '17 at 16:32

score 2 · Answer 2 · answered Dec 20 '13 at 15:22

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

As I said in my question: format needs explicit arguments, I can't do select format(message, arr[1], arr[2]) from table because message in the table sometimes needs 2 and sometimes needs 4 parameters from the array. — MarcinJ, Dec 20 '13 at 15:36
Correct. Which is why you'll need to run a convoluted query such as `select escape_literal(val) || ', ' from unnest(arr) val;` in order to build a `_values_string` variable, and then you'll go something like: `execute 'select format(' || escape_literal(_format_string) || ', ' || _values_string || ')'` — or something to that order. It'll be *very* ugly. And slow. Imho, you probably should revisit the idea and use the built-in `format()` function. — Denis de Bernardy, Dec 20 '13 at 15:44

Postgres format string using array

2 Answers2

Linked