And for today I have yet another short PostgreSQL tip.
I use a database which has quite a few tables with jsonb – and also json – type fields. (I know, I know. No new json columns, but legacy etc.) Since they often contain quite a few things, pretty printing them is something I tend to do fairly often. Happily, PostgreSQL has a function to do that. Less happily, it only works with jsonb columns, and has a long, awkward name: jsonb_pretty.
But it’s no longer a problem for me, since it is easy to create a shorthand. AFAIK, PostgreSQL does not support function aliases, but I can just define a new function doing the same thing. And while at that, I can also use the perhaps lesser-known feature of PostgreSQL functions (actually, I think it’s even in the SQL standard, so it’s not even a PostgreSQL extension), namely overloading. So, here is how I can define two functions with the same name, one accepting a jsonb argument and another accepting a json argument:
create function pretty(arg json) returns text as 'select jsonb_pretty(arg::jsonb)' language sql immutable returns null on null input; create function pretty(arg jsonb) returns text as 'select jsonb_pretty(arg)' language sql immutable returns null on null input;
Of course, the immutable and returns null on null input clauses are there just for optimization.