Postgres format string using array
arrays, format, postgresql
Solution
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)
Problem
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.