When we use the format() function in PostgreSQL, we can pass any number of strings to insert into the resulting formatted string in positions that are specified in the first argument. When we do this, we might typically pass the strings as separate arguments. But this isn’t the only way to do it.

We have the option of passing the strings as array elements. That is, we can pass an array to the function, and have it extract each array element as though it’s a separate argument to insert into the formatted string.

The reason we can do this is because the format() function is variadic. This means it accepts a variable number of arguments. If we use the VARIADIC keyword when passing an array, this will result in each array element being treated as a separate argument to insert into the formatted string.

Example

Here’s an example to demonstrate:

SELECT format(
    'I have a %s, a %s, and a %s', 
    VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ]
    );

Result:

I have a Cat, a Dog, and a Horse

Here I used the VARIADIC keyword along with the ARRAY constructor as the second argument to the format() function. This resulted in each array element being treated as a separate string to be inserted into the output string.

This is basically the same effect as passing each array element as a separate argument:

SELECT format(
    'I have a %s, a %s, and a %s', 
    'Cat', 'Dog', 'Horse'
    );

Result:

I have a Cat, a Dog, and a Horse

Changing the Position of Elements

We can switch the position of each element if we want:

SELECT format(
    'I have a %2$s, a %3$s, and a %1$s', 
    VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ]
    );

Result:

I have a Dog, a Horse, and a Cat

SQL Literals

We can also do other stuff, like use the array elements as SQL literals:

SELECT format(
    'SELECT * FROM t1 WHERE c2 = %2$L', 
    VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ]
    );

Result:

SELECT * FROM t1 WHERE c2 = 'Dog'

SQL Identifiers

We can even use the array elements as SQL identifiers:

SELECT format(
    'SELECT * FROM %2$I', 
    VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ]
    );

Result:

SELECT * FROM "Dog"