PostgreSQL Dynamic SQL & Quote_ident Gotchas

PostgreSQL provides two useful functions to aid in the safe development of dynamic SQL:
quote_ident
The quote_ident function accepts and returns a text type. Whatever text you pass into quote_ident will be suitably escaped such that you could safely use it as an identifier within a dynamic SQL statement.

According to the documentation, you should always pass table and column identifiers into the quote_ident function for safety. Calling quote_ident('mytable') will return mytable, however calling quote_ident('MyTable') would return "MyTable".

quote_literal

The quote_literal function accepts and returns a text type. Whatever text you pass into the quote_literal function will be escaped so that you can safely use them in dynamic SQL.

You should always pass values or literals into the quote_literal function. By doing so, all special characters such as quotes will be safely dealt with. Calling quote_literal('mycolumn') would return 'mycolumn' whilst quote_literal('my'column') would return 'my''column'.

[@more@]Both of these functions work a treat, however there is a caveat with the quote_ident function which isn’t well documented. When creating objects in PostgreSQL, they object names are automatically lowercased unless you create the object using double quotes. As a simple example:

1. CREATE TABLE MyTable (id integer, name varchar); would result in an object mytable being created.
2. CREATE TABLE "MyTable" (id integer, name varchar); would result in an object MyTable being created; note the casing.

Now lets assume you wanted to create some dynamic SQL to fetch information out of the first example table above. If you issued quote_ident('mytable'), your dynamic SQL statement will execute because the value returned from quote_ident is lowercase which matches the table name. If you called quote_ident('MyTable'), your dynamic SQL statement will report an error stating that it cannot find the table or relation.

Creating dynamic SQL in PostgreSQL to fetch data out of the second example above, you would run into the reverse scenario. Issuing quote_ident('mytable') would produce an error, while quote_ident('MyTable') would execute without error.

If you create your database objects without using double quotes, then it’s important to remember to not pass capitalised parameters into quote_ident. The opposite is of course true as well, if you create your objects using double quotes then you must remember to pass in the same casing to quote_ident. If quote_ident applies double quotes (be it from capitised letters, spaces or special characters), the SQL engine within PostgreSQL will assume that an object exists with the explicit name matching the returned value of the quote_ident function.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1697933/viewspace-897423/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/1697933/viewspace-897423/