Sprintf

There are two versions, one for 8.3 and earlier and one for 8.4, below.
This is the 8.3 version:
create or replace function printf(fmt text, args text[]) 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;
$$;
It's called like this:
 # select printf('% is a % and % is a %, but %% is a %', '{hello,word,1,number,percent sign}');
 printf
 ------------------------------------------------------------
 hello is a word and 1 is a number, but % is a percent sign
 (1 fila)
The 8.4 version uses a VARIADIC argument:
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;
$$;
It's called like this:
 # 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)
You can print more complex stuff such as records by using the textin(sometype-out-func(object)) notation. For records it looks like this:
 alvherre=# select printf('the % row looks like %',
 oid::regclass::text, textin(record_out(pg_class.*)))
 from pg_class limit 10;
 printf
 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 the pg_type row looks like (pg_type,11,71,10,0,1247,0,7,283,0,0,t,f,f,r,28,0,t,f,f,f,f,648,{=r/alvherre},)
 the information_schema.user_mapping_options row looks like (user_mapping_options,11326,11551,10,0,11550,0,0,0,0,0,f,f,f,v,5,0,f,f,t,f,f,0,"{alvherre=arwdDxt/alvherre,=r/alvherre}",)
 the information_schema.user_mappings row looks like (user_mappings,11326,11555,10,0,11554,0,0,0,0,0,f,f,f,v,3,0,f,f,t,f,f,0,"{alvherre=arwdDxt/alvherre,=r/alvherre}",)
 the information_schema.triggered_update_columns row looks like (triggered_update_columns,11326,11495,10,0,11494,0,0,0,0,0,f,f,f,v,7,0,f,f,t,f,f,0,"{alvherre=arwdDxt/alvherre,=r/alvherre}",)
 the information_schema.triggers row looks like (triggers,11326,11498,10,0,11497,0,0,0,0,0,f,f,f,v,17,0,f,f,t,f,f,0,"{alvherre=arwdDxt/alvherre,=r/alvherre}",)
 the information_schema.usage_privileges row looks like (usage_privileges,11326,11502,10,0,11501,0,0,0,0,0,f,f,f,v,8,0,f,f,t,f,f,0,"{alvherre=arwdDxt/alvherre,=r/alvherre}",)
 the information_schema.view_column_usage row looks like (view_column_usage,11326,11506,10,0,11505,0,0,0,0,0,f,f,f,v,7,0,f,f,t,f,f,0,"{alvherre=arwdDxt/alvherre,=r/alvherre}",)
 the information_schema.view_routine_usage row looks like (view_routine_usage,11326,11510,10,0,11509,0,0,0,0,0,f,f,f,v,6,0,f,f,t,f,f,0,"{alvherre=arwdDxt/alvherre,=r/alvherre}",)
 the information_schema.view_table_usage row looks like (view_table_usage,11326,11514,10,0,11513,0,0,0,0,0,f,f,f,v,6,0,f,f,t,f,f,0,"{alvherre=arwdDxt/alvherre,=r/alvherre}",)
 the information_schema.views row looks like (views,11326,11518,10,0,11517,0,0,0,0,0,f,f,f,v,7,0,f,f,t,f,f,0,"{alvherre=arwdDxt/alvherre,=r/alvherre}",)
(10 filas)
This work was sponsored by CashNetUSA.
Category:PL/pgSQL