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.
VARIADIC is a modifier for array input into functions. format() happens to use it. The manual:
The
concat,concat_wsandformatfunctions arevariadic, so it is possible to pass the values to be concatenated or formatted as an array marked with theVARIADICkeyword (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.
(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[]);
Related: