Waiting for 9.1 – format()

On 21st of November Robert Haas committed new patch, which adds new function:

Add new SQL function, format(text).

Currently, three conversion format specifiers are supported: %s for a
string, %L for an SQL literal, and %I for an SQL identifier.  The latter
two are deliberately designed not to overlap with what sprintf() already
supports, in case we want to add more of sprintf()'s functionality here
later.
 
Patch by Pavel Stehule, heavily revised by me.  Reviewed by Jeff Janes
and, in earlier versions, by Itagaki Takahiro and Tom Lane.

Functionality is clearly defined, but what good it is for you? Answer is very simple – this will greatly help with readability of functions which create dynamic statements, with possible additional point of making them robust.

For example – let's sat you want (for whatever reason) to write a function that returns max() value of given column in given table.

Such function could be written as:

CREATE FUNCTION get_max( in_table_name TEXT, in_column_name TEXT ) RETURNS TEXT AS $$
DECLARE
    reply TEXT;
BEGIN
    EXECUTE 'SELECT max(' || quote_ident( in_column_name ) || ') FROM ' || quote_ident( in_table_name ) INTO reply;
    RETURN reply;
END;
$$ LANGUAGE plpgsql;

Which isn't bad, but is also not really easy on eyes.

With new format() function I can:

CREATE FUNCTION get_max( in_table_name TEXT, in_column_name TEXT ) RETURNS TEXT AS $$
DECLARE
    reply TEXT;
BEGIN
    EXECUTE format( 'SELECT max(%I) FROM %I', in_column_name, in_table_name) INTO reply;
    RETURN reply;
END;
$$ LANGUAGE plpgsql;

Which is pretty cool, as in many places I saw people skipping quote_ident() because “they could trust the input", and syntax with || quote_ident(…) || is just too long.

Now, there is no point in skipping quotation, as it is done automatically. And resulting code is definitely much more readable.

If we could just add to this ability to used named placeholders (with hstore) – it would be really nice. And if we could have all sprintf() functionalities – it would be simply amazing.

11 thoughts on “Waiting for 9.1 – format()”

  5. Is it possible to supply a parameter index in format string like in perl version of sprintf?
    >> printf ‘%2$d %1$d’, 12, 34; # prints “34 12”

  7. Great!
    I was using your implementation of printf using perl sprintf function. Soon there will be no need to use workarounds and plperl dependency will be removed.

  8. @Kop:
    there is sprintf-alike in plpgsql (linked in comments to the printf blogpost). but plperl is good to have as it has other uses too (like – not-broken regexp engine).

  9. #KOP you can find a relative well native implementation of sprintf on net. I am against to moving this function to core, because sprintf has a little bit strange API related to C language. But when I wrote this code, then is useless put it to /dev/zero. I’ll move it to psttools. So if you like, then you can use a sprintf from third party contrib module early.

  10. Good job!

    But I have a trouble even with your example:

    tmp=# SELECT * from t1;
    bla
    —–
    1
    2
    3
    1
    2
    (5 rows)

    echelon=# select get_max(‘tmk’, ‘k’);
    ERROR: function format(unknown, text, text) does not exist
    LINE 1: SELECT format( ‘SELECT max(%I) FROM %I’, in_column_name, in_…
    ^
    HINT: No function matches the given name and argument types. You might need to add explicit type casts.
    QUERY: SELECT format( ‘SELECT max(%I) FROM %I’, in_column_name, in_table_name)
    CONTEXT: PL/pgSQL function “get_max” line 4 at EXECUTE statement

    psql (PostgreSQL) 9.0.4, Linux, mint.

    Any ideas?

  11. @Alex:
    please note that the title of the blogpost is: waiting for *9.1* – that means that this functionality will be in 9.1, and not 9.0

Comments are closed.