sprintf in SQL
Ever needed to print out something with embedded data? Not an uncommon requirement, I'm afraid. Most people just concatenate stuff together:
SELECT 'A number ' || 42 || ' is a ' || 21 || ' * ' || 2 || ' by any other name'
Eventually it turns out confusing, as it's easy to visually mix the concatenation operator || with the quotes.
PL/pgSQL offers a printf of sorts ... except that you can't use it everywhere you'd like:
RAISE NOTICE ' A number % is a % * % by any other name', 42, 21, 2;
How to capture such a thing so that you can, for instance, use that as a return value? Well, that's what most languages call an "sprintf" function or %-expansion. It's a trivial technique really.
So you want it in SQL? Well, here it is, as a PL/pgSQL function for maximum portability.
CREATE OR REPLACE FUNCTION printf(fmt text, variadic args anyarray) returns text
language plpgsql AS $$
DECLARE
argcnt int = 1;
chrcnt int = 0;
fmtlen int;
CHR text;
output text = '';
BEGIN
fmtlen = LENGTH(fmt);
LOOP
chrcnt = chrcnt + 1;
-- ran out of format string? bail out
IF chrcnt > fmtlen THEN
EXIT;
END IF;
-- grab our char
CHR = substring(fmt, chrcnt, 1);
-- %% means output a single %, and skip them
IF CHR = '%' AND substring(fmt, chrcnt + 1, 1) = '%' THEN
output = output || '%';
chrcnt = chrcnt + 1;
continue;
END IF;
-- a % on its own means output an element from our arg list
IF CHR = '%' THEN
output = output || COALESCE(args[argcnt]::text, '');
argcnt = argcnt + 1;
continue;
END IF;
-- no special case? output the thing
output = output || CHR;
END LOOP;
RETURN output;
END;
$$;
This is not as flexible as the true sprintf function found in C and variants, which allow you to do neat stuff like fill some variables to fixed widths, or specify number of digits after the decimal point for non-integer values. But it's already quite useful. Furthermore, if you really need a given number of decimal you can add a cast to NUMERIC, which you can't do in C ...
The biggest problem it has is that any argument that's not implicitly castable to text must carry an explicit cast. The good thing is that by adding casts you can even print out complex structures like records or arrays.
# select printf('% is a % and % is a %, but %% is a %',
'hello', 'word', 1::text, 'number', 'percent sign');
printf
------------------------------------------------------------
hello is a word and 1 is a number, but % is a percent sign
(1 fila)
alvherre=# select printf('% %% is %',
3.01110001100101::float::numeric(10,2)::text,
ROW(1,2,3,'foo bar')::text);
printf
-----------------------------
3.01 % is (1,2,3,"foo bar")
(1 fila)
I'm sure you can find more interesting uses that than one!
I've uploaded this function to our growing collection of code snippets in our Wiki, so that it won't be lost in the dark corners of this blog.