Sprintf

Snippets

sprintf

Works with PostgreSQL

Any version

Written in

PL/pgSQL

Depends on

Nothing

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.