{"id":5011,"date":"2013-08-26T08:29:35","date_gmt":"2013-08-26T16:29:35","guid":{"rendered":"http:\/\/www.chesnok.com\/daily\/?p=5011"},"modified":"2013-08-26T08:35:51","modified_gmt":"2013-08-26T16:35:51","slug":"fancy-sql-monday-format-instead-of-quote_","status":"publish","type":"post","link":"https:\/\/www.chesnok.com\/daily\/2013\/08\/26\/fancy-sql-monday-format-instead-of-quote_\/","title":{"rendered":"Fancy SQL Monday: format() instead of quote_*()"},"content":{"rendered":"

In the comments, Isaac pointed out that using format()<\/code><\/a> dramatically increases the readability of SQL. I liked the look of his query, so I dug a little deeper.<\/p>\n

As of version 9.1 (first released in 2010), a new function is listed in Postgres’ built-in string function documentation<\/a>:<\/p>\n

\n

format(formatstr text [, str “any” [, …] ]): Format a string. This function is similar to the C function sprintf; but only the following conversion specifications are recognized: %s interpolates the corresponding argument as a string; %I escapes its argument as an SQL identifier; %L escapes its argument as an SQL literal; %% outputs a literal %. A conversion can reference an explicit parameter position by preceding the conversion specifier with n$, where n is the argument position.<\/p>\n<\/blockquote>\n

We also have examples linked in the definition for various quoting strategies<\/a> for dynamic SQL.<\/p>\n

This is an example where the Postgres documentation probably should have reversed the order what is mentioned.<\/p>\n

It turns out that format()<\/code> makes it much easier to avoid using the quote_*()<\/code> functions. The code looks a lot more like a python \"\"\"<\/code> string (you can have arbitrary whitespace in there!), with flexible options for usage. The only feature missing is named parameters.<\/p>\n

My application requires Postgres 9.2 at this point (for JSON datatype), so my plan is to refactor a few functions using format() instead of quote_ident()<\/code> in particular.<\/p>\n

Are there situations where you’d prefer to use quote_*()<\/code> other than for backward compatibility? It seems as though format()<\/code> is far safer, particularly for the quoting and nullable problems mentioned on the Quote Literal Example<\/a> documentation.<\/p>\n","protected":false},"excerpt":{"rendered":"

In the comments, Isaac pointed out that using format() dramatically increases the readability of SQL. I liked the look of his query, so I dug a little deeper. As of version 9.1 (first released in 2010), a new function is … Continue reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-5011","post","type-post","status-publish","format-standard","hentry","category-postgresql"],"_links":{"self":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5011","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/comments?post=5011"}],"version-history":[{"count":5,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5011\/revisions"}],"predecessor-version":[{"id":5016,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5011\/revisions\/5016"}],"wp:attachment":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/media?parent=5011"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/categories?post=5011"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/tags?post=5011"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}