Hi,
A few months ago, I got an email related to Pyrseas
(http://lists.pgfoundry.org/pipermail/pyrseas-general/2011-August/000003.html)
where the user reported he had a table named "user" and reported a
failure in the dbtoyaml utility. I eventually implemented a simple
quote_id function (only checks if an identifier needs quoting based on
the characters in it, not on whether it's a keyword or not). I left
adding a list of keywords as a "to do." Filip, the user, recommended
that Pyrseas "quote all identifiers sent to database."
I was reluctant to go with the latter solution, but after two other
issues (Tryton database had a column named "limit" and another user a
table named "order"), I started making the changes. However, I've found
some PostgreSQL behavior that is inconsistent, at the very least with
respect to the documentation. It's probably best shown by example
(tested under 8.4 and 9.1):
pyrseas_testdb=# CREATE FUNCTION "f1"("integer", "integer") RETURNS
"integer" LANGUAGE "sql" AS $_$SELECT GREATEST($1, $2)$_$;
ERROR: type integer does not exist
pyrseas_testdb=# CREATE TABLE "t1" ("c1" "integer", "c2" "text");
ERROR: type "integer" does not exist
LINE 1: CREATE TABLE "t1" ("c1" "integer", "c2" "text");
^
pyrseas_testdb=# CREATE TABLE "t1" ("c1" integer, "c2" "text");
CREATE TABLE
pyrseas_testdb=# CREATE DOMAIN "d1" AS integer;
CREATE DOMAIN
pyrseas_testdb=# CREATE TABLE "t2" ("c1" "d1", "c2" "text");
CREATE TABLE
pyrseas_testdb=# CREATE TABLE "t3" ("c1" "int4", "c2" "text");
CREATE TABLE
pyrseas_testdb=# CREATE TABLE "t4" ("c1" "int", "c2" "text");
ERROR: type "int" does not exist
LINE 1: CREATE TABLE "t4" ("c1" "int", "c2" "text");
^
pyrseas_testdb=# CREATE TABLE "t5" ("c1" "INTEGER", "c2" "text");
ERROR: type "INTEGER" does not exist
LINE 1: CREATE TABLE "t5" ("c1" "INTEGER", "c2" "text");
^
pyrseas_testdb=# CREATE DOMAIN "float" AS real;
CREATE DOMAIN
pyrseas_testdb=# CREATE TABLE "t6" ("c1" "float", "c2" "text");
CREATE TABLE
pyrseas_testdb=# DROP TABLE "t6";
pyrseas_testdb=# DROP DOMAIN "float";
DROP DOMAIN
pyrseas_testdb=# CREATE TABLE "t6" ("c1" "float", "c2" "text");
ERROR: type "float" does not exist
LINE 1: CREATE TABLE "t6" ("c1" "float", "c2" "text");
^
The last part is a killer. If "float" is a domain, then it can be
quoted, otherwise it can't. The documentation appears to contradict
this.
http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
says FLOAT is not reserved and cannot be a function or type. I tried
defining a "float" function and it was accepted:
pyrseas_testdb=# CREATE FUNCTION "float"(integer) returns real language
sql as $_$select $1::real$_$;
CREATE FUNCTION
It seems to me that since a TYPE in a column definition or function
argument can be a non-native TYPE, it could be a reserved word and
therefore it should always be allowable to quote the TYPE. Can someone
please explain why that is not the case?
Joe
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
