How to integrate a CASE construct with the VARIADIC keyword as input to format()

Question

How can I use VARIADIC to pass an array of arguments to the FORMAT() function depending on a CASE condition in PostgreSQL 11?

This, for instance, works:

SELECT FORMAT('Hi %s, I am %s', VARIADIC ARRAY['John', 'Paul']);

Hi John, I am Paul

This also works:

SELECT FORMAT('Hello %s, I am %s.',
              CASE
                WHEN 1 = 1 THEN 'John'
                ELSE 'Mary'
              END,
             CASE
                WHEN 1 = 1 THEN 'Paul'
                ELSE 'Elli'
              END);

Hello John, I am Paul.

This, however, doesn't:

SELECT FORMAT('Hello %s, I am %s.',
              CASE
                WHEN 1 = 1 THEN VARIADIC ARRAY['John', 'Paul']
                ELSE VARIADIC ARRAY['Mary', 'Elli']
              END);

ERROR: syntax error at or near "VARIADIC" LINE 3: WHEN 1 = 1 THEN VARIADIC ARRAY['John', 'Paul...

Theoretically it should work, and if it does it'll save me from repeating CASE as many times as the number of parameters I have to pass. I don't want to surround FORMAT with CASE because my string is huge.

Erwin Brandstetter · Accepted Answer · 2021-11-11T13:04:36.973

VARIADIC is a modifier for array input into functions. format() happens to use it. The manual:

The concat, concat_ws and format functions are variadic, so it is possible to pass the values to be concatenated or formatted as an array marked with the VARIADIC keyword (see Section 38.5.5).

The same is not true for a CASE construct, which isn't even a function to begin with. There is still a simple solution:

SELECT format('Hello %s, I am %s.', VARIADIC  -- here!
              CASE
                WHEN true THEN ARRAY['John', 'Paul']
                ELSE ARRAY['Mary', 'Elli']
              END);

You just misplaced the keyword. VARIADIC is an input modifier. Not applicable to output.

Combine array and non-array parameters?

(Answer to question in the comment.)
It's not possible to pass individual strings and an array at the same time. Concatenate all arguments to a single array before passing in VARIADIC mode:

SELECT format E'Title: %s\nHi %s, I am %s.'::text, VARIADIC
'MyTitle'::text || ARRAY['John', 'Paul']);

Or put everything into an array to begin with, if you are in control of parameters. Demonstrating an array literal this time:

SELECT format(E'Title: %s\nHi %s, I am %s.'::text,  VARIADIC '{MyTitle, John, Paul}'::text[]);

Thank you. Does one have to put all of the arguments inside one array now for `format()` to accept them? or can one pass some unconditional parameters to `format()` and pass only the conditional parameters in a `VARIADIC` `ARRAY`? This is giving an error `SELECT FORMAT(E'Title: %s\nHi %s, I am %s.'::text, 'MyTitle'::text, VARIADIC ARRAY['John', 'Paul']);` — Rafs, Nov 10 '21 at 09:46
That's it, I ended up making one `ARRAY` but wanted to make sure I am not missing anything. Thank you again! — Rafs, Nov 11 '21 at 09:59

How to integrate a CASE construct with the VARIADIC keyword as input to format()

1 Answers1

Combine array and non-array parameters?