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.

CodePudding user response：

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.