| Subject: | column_info() should not return pre-quoted identifiers quoting (using PgSQL 8's quote_ident()) |
I think there is a bug in DBD::Pg's column_info() function.
I use the COLUMN_NAME field of the column_info() function to dynamically
retrieve the column names of my database tables. Up to PostgreSQL 7.4
(using the latest DBD::Pg, 1.43), things have been working fine in my
application:
| $ perl -MDBI -e '$dbh = DBI->connect("dbi:Pg:dbname=usm");
| $sth = $dbh->column_info(undef, "class", "account", "user");
| print($sth->fetchrow_hashref->{COLUMN_NAME}, "\n")'
| user
Later in my code, when I need to use the column name in database
statements, I use the quote_identifier() function to quote it. For
other purposes, I use the unquoted column name.
When I looked into DBD::Pg's column_info() function, I saw that it uses
the quote_ident() SQL function to quote the schema, table, and column
identifiers.
In PostgreSQL 7.4, the quote_ident() doesn't recognize the "user"
keyword and thus returns q(user) just as if it was a random identifier:
| usm=> \t
| Showing only tuples.
| usm=> SELECT quote_ident('user');
| user
| usm=> SELECT quote_ident('foobar');
| foobar
| usm=> SELECT count(*) FROM class.user;
| ERROR: syntax error at or near "user" at character 28
As a result, everything worked as I expected under PostgreSQL 7.4. (In
fact I never expected column_info() to return already quoted identifier
names. This also isn't documented anywhere.)
Today I upgraded to PostgreSQL 8, and things broke:
| $ perl -MDBI -e '$dbh = DBI->connect("dbi:Pg:dbname=usm");
| $sth = $dbh->column_info(undef, "class", "account", "user");
| print($sth->fetchrow_hashref->{COLUMN_NAME}, "\n")'
| "user"
Obviously, the unquoted column name is now q("user") instead of q(user),
while DBD::Pg's quote_identifier(q("user")) yields q("""user"""). As a
result, my code breaks.
In PostgreSQL 8, the quote_ident() SQL function has been fixed[1]:
| usm=> \t
| Showing only tuples.
| usm=> SELECT quote_ident('user');
| "user"
| usm=> SELECT quote_ident('foobar');
| foobar
| usm=> SELECT count(*) FROM class.user;
| 12
(Interestingly, PostgreSQL 8 now also correctly deduces that the "user"
part in "class.user" cannot mean the "user" keyword and so no longer
requires it to be quoted in this context.)
Now I'm wondering why DBD::Pg's table_info() and column_info() functions
return quoted identifier names in the first place. I don't think this
is what they should be doing, since there is no unquote_identifier()
function, so the quoting cannot be undone in a portable way.
I consider this a (design) bug in DBD::Pg, and not because it breaks my
code (which can be changed with far less trouble than DBD::Pg's behavior
in this regard probably can).
References:
1. http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/
utils/adt/quote.c#rev1.15