Paul Jungwirth wrote > I'm not sure how to make a prepared statement that lets you name a > column when you execute it. Maybe someone else can chime in if that's > possible.
David J. responded > You cannot. By definition parameters, in this context, are values - not > identifiers. > [...] > In both situations there is no way for the planner to plan and cache a > single query whose order by column varies. No matter what you do at best > you can have a single plan for each explicit order by column that you wish > to specify.
That's what I'd figured. The motivation to use prepared statements in application layers is not so much having a single plan but more the insulation from SQL injection. The intent of the given ORDER BY example was to restricts inputs to valid identifiers rather than part of the query expression.
Maybe what we need in ODBC libs and the like is a "protected statement" that follows the same construction as a prepared statement but additionally checks catalogs to validate identifiers. Bryn
The canonical way to do this, in reasonably recent PostgreSQL versions, is to wrap your desired dynamic SQL statement in a function. Within that function construct the SQL string with the assistance of the "format(...)" function. That function has specific placeholders for literals and identifiers that will ensure that the constructed SQL string is built in a safe manner.
Then you call the function and pass in the arguments are value parameters; which the function then converts into either literal or identifiers as instructed to by the format expression.
