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: