current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 1 down vote favorite
1

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.

share|improve this question
    
if you can, check orafce extension 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
    
Orafce is in RHEL or DEB repositories. – Pavel Stehule Dec 20 '13 at 18:49
up vote 4 down vote accepted

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)
share|improve this answer
    
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 (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. – Ondrej Burkert Nov 11 '14 at 14:14
up vote 0 down vote

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.