Craig Ringer <craig@2ndquadrant.com> writes:
> While looking at an unrelated issue in PgJDBC I noticed that it's
> difficult for users and the driver to tell in advance if a given
> statement will support bind parameters.
It's not that hard ;-) ... if it ain't SELECT/INSERT/UPDATE/DELETE,
it won't accept parameters.
> As a result, some code that worked with PgJDBC using the v2 protocol
> will fail with the v3 protocol, e.g.
> @Test
> public void test() throws SQLException {
> PGConnection pgc = (PGConnection)conn;
> PreparedStatement ps = conn.prepareStatement("SET ROLE ?");
> ps.setString(1, "somebody");
> ps.executeUpdate();
> }
It's more or less accidental that that works, I think. I assume that the
statement that actually gets sent to the server looks like
SET ROLE 'something'
which morally ought to be a syntax error: you'd expect the role name
to be an identifier (possibly double-quoted). Not a singly-quoted string
literal. We allow a string literal because for some weird reason the SQL
standard says so, but it still feels like a type violation.
> It'd be nice not to force users to do their own escaping of literals in
> non-plannable statements. Before embarking on anything like this I
> thought I'd check and see if anyone's looked into supporting bind
> parameters in utility statements, or if not, if anyone has any ideas
> about the feasibility of adding such support.
I think it might be desirable but it'd be a mess, both as to the
concept/definition and as to the implementation. How would a parameter
placeholder substitute for an identifier --- for example, what type would
be reported by "Describe"? What would you do about parameter placeholders
in expressions in DDL --- for example,
CREATE TABLE mytable (f1 int default ?+? );
Here, the placeholders surely don't represent identifiers, but the system
is going to have a hard time figuring out what datatype they *should*
represent. Carrying that example a bit further, I wonder what the chances
are of doing something sane or useful with
CREATE TABLE ? (? ? default ?+? );
But if you want to punt on that, I think you just greatly weakened your
argument for the whole thing.
On the implementation side, I'm worried about how we make sure that
parameter placeholders get replaced in a DDL expression that would
normally *not* get evaluated immediately, like the DEFAULT expression
above.
regards, tom lane
