Did it help? If yes - maybe you can help me?
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
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.