11

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.

Improve this question
3
  • if you can, check orafce extension github.com/orafce/orafce - plvsubst.string is exactly what you want Commented Dec 20, 2013 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. Commented Dec 20, 2013 at 18:44
  • Orafce is in RHEL or DEB repositories. Commented Dec 20, 2013 at 18:49

2 Answers 2

Reset to default
20

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)
Improve this answer
6
  • 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 Commented Nov 11, 2014 at 11:37
  • I just found a way for my problem elsewhere (stackoverflow.com/questions/7914325/…) So I use '($1).col and then using NEW. We don't delete so we don't need to care about NEW/OLD distinction. Commented Nov 11, 2014 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' Commented Jan 6, 2017 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:] Commented Jan 7, 2017 at 5: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; Commented Jan 9, 2017 at 16:32
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

Improve this answer
2
  • 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. Commented Dec 20, 2013 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. Commented Dec 20, 2013 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.