CREATE OR REPLACE FUNCTION printf2(fmt text, variadic args anyarray) RETURNS text
LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
argcnt int := 1;
head text := ''; -- result
tail text := fmt; -- unprocessed part
k int;
BEGIN
LOOP
k := strpos(tail, '%');
IF k = 0 THEN
-- no more '%'
head := head || tail;
EXIT;
ELSE
IF substring(tail, k+1, 1) = '%' THEN
-- escape sequence '%%'
head := head || substring(tail, 1, k);
tail := substring(tail, k+2);
ELSE
-- insert argument
head := head || substring(tail, 1, k-1) || COALESCE(args[argcnt]::text, '');
tail := substring(tail, k+1);
argcnt := argcnt + 1;
END IF;
END IF;
END LOOP;
RETURN head;
END;
$$;
wtorek, 24 sierpnia 2010
PostgrSQL: printf in PL/pgSQL
PostgreSQL wiki has entry about sprintf - is is quite simple approach (and isn't marked as immutable), the main drawback is iterating over all chars of format string. Here is a version use strpos to locate % in format string, and it's faster around 2 times: