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.