作者
digoal
日期
2017-10-24
标签
PostgreSQL , keywords , 关键字 , Oracle 兼容性
背景
PostgreSQL数据库中有哪些关键字，这些关键字的使用限制如何？
https://www.postgresql.org/docs/10/static/sql-keywords-appendix.html
文档中的说明并不是特别清晰，（并且KEYWORDS与版本强相关），所以使用pg_get_keywords这个系统函数得到的，更加准确可读。
https://www.postgresql.org/docs/10/static/functions-info.html
```
pg_get_keywords() returns a set of records describing the SQL keywords recognized by the server.
1、The word column contains the keyword.
2、The catcode column contains a category code:
U for unreserved, C for column name, T for type or function name, or R for reserved.
3、The catdesc column contains a possibly-localized string describing the category.
```
关键字查找
keyword不同的分类，含义不一样。
postgres=# select * from pg_get_keywords();
word | catcode | catdesc
-------------------+---------+----------------------------------------------
abort | U | unreserved
absolute | U | unreserved
access | U | unreserved
action | U | unreserved
add | U | unreserved
admin | U | unreserved
.............
例子，使用keywrod作为identity的报错示例：
postgres=# \set VERBOSITY verbose
postgres=# create table cast (id int);
ERROR: 42601: syntax error at or near "cast"
LINE 1: create table cast (id int);
^
LOCATION: scanner_yyerror, scan.l:1086
注意：keyword的分类解释
1、unreserved，不保留，可以用于任何identity（视图、表、函数、类型、索引、字段、类型 等名称）。
2、reserved，保留，不可用于任何identity。
3、reserved (can be function or type name)，保留，但是可用于函数、类型名。
4、unreserved (cannot be function or type name)，不保留，但是不可用于函数、类型名。
理解了这几类意思，你就知道keyword什么地方能用，什么地方不能用了。
keyword相关代码
src/backend/utils/adt/misc.c
/* Function to return the list of grammar keywords */
Datum
pg_get_keywords(PG_FUNCTION_ARGS)
{
..........
switch (ScanKeywords[funcctx->call_cntr].category)
{
case UNRESERVED_KEYWORD:
values[1] = "U";
values[2] = _("unreserved");
break;
case COL_NAME_KEYWORD:
values[1] = "C";
values[2] = _("unreserved (cannot be function or type name)");
break;
case TYPE_FUNC_NAME_KEYWORD:
values[1] = "T";
values[2] = _("reserved (can be function or type name)");
break;
case RESERVED_KEYWORD:
values[1] = "R";
values[2] = _("reserved");
break;
default: /* shouldn't be possible */
values[1] = NULL;
values[2] = NULL;
break;
}
...........
src/include/common/keywords.h
```
/ Keyword categories --- should match lists in gram.y /
define UNRESERVED_KEYWORD 0
define COL_NAME_KEYWORD 1
define TYPE_FUNC_NAME_KEYWORD 2
define RESERVED_KEYWORD 3
```
src/include/parser/kwlist.h
/* name, value, category */
PG_KEYWORD("abort", ABORT_P, UNRESERVED_KEYWORD)
PG_KEYWORD("absolute", ABSOLUTE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("access", ACCESS, UNRESERVED_KEYWORD)
PG_KEYWORD("action", ACTION, UNRESERVED_KEYWORD)
....................
PG_KEYWORD("xmlparse", XMLPARSE, COL_NAME_KEYWORD)
PG_KEYWORD("xmlpi", XMLPI, COL_NAME_KEYWORD)
PG_KEYWORD("xmlroot", XMLROOT, COL_NAME_KEYWORD)
PG_KEYWORD("xmlserialize", XMLSERIALIZE, COL_NAME_KEYWORD)
PG_KEYWORD("xmltable", XMLTABLE, COL_NAME_KEYWORD)
PG_KEYWORD("year", YEAR_P, UNRESERVED_KEYWORD)
PG_KEYWORD("yes", YES_P, UNRESERVED_KEYWORD)
PG_KEYWORD("zone", ZONE, UNRESERVED_KEYWORD)
plpgsql中的保留字
src/pl/plpgsql/src/pl_scanner.c
```
/
* A word about keywords:
* We keep reserved and unreserved keywords in separate arrays. The
* reserved keywords are passed to the core scanner, so they will be
* recognized before (and instead of) any variable name. Unreserved words
* are checked for separately, usually after determining that the identifier
* isn't a known variable name. If plpgsql_IdentifierLookup is DECLARE then
* no variable names will be recognized, so the unreserved words always work.
* (Note in particular that this helps us avoid reserving keywords that are
* only needed in DECLARE sections.)
* In certain contexts it is desirable to prefer recognizing an unreserved
* keyword over recognizing a variable name. In particular, at the start
* of a statement we should prefer unreserved keywords unless the statement
* looks like an assignment (i.e., first token is followed by ':=' or '[').
* This rule allows most statement-introducing keywords to be kept unreserved.
* (We still have to reserve initial keywords that might follow a block
* label, unfortunately, since the method used to determine if we are at
* start of statement doesn't recognize such cases. We'd also have to
* reserve any keyword that could legitimately be followed by ':=' or '['.)
* Some additional cases are handled in pl_gram.y using tok_is_keyword().
* We try to avoid reserving more keywords than we have to; but there's
* little point in not reserving a word if it's reserved in the core grammar.
* Currently, the following words are reserved here but not in the core:
* BEGIN BY DECLARE EXECUTE FOREACH IF LOOP STRICT WHILE
*/
/
* Lists of keyword (name, token-value, category) entries.
* !!WARNING!!: These lists must be sorted by ASCII name, because binary
* search is used to locate entries.
* Be careful not to put the same word in both lists. Also be sure that
* pl_gram.y's unreserved_keyword production agrees with the second list.
/
```
```
..........
static const ScanKeyword reserved_keywords[] = {
PG_KEYWORD("all", K_ALL, RESERVED_KEYWORD)
PG_KEYWORD("begin", K_BEGIN, RESERVED_KEYWORD)
PG_KEYWORD("by", K_BY, RESERVED_KEYWORD)
PG_KEYWORD("case", K_CASE, RESERVED_KEYWORD)
PG_KEYWORD("declare", K_DECLARE, RESERVED_KEYWORD)
PG_KEYWORD("else", K_ELSE, RESERVED_KEYWORD)
PG_KEYWORD("end", K_END, RESERVED_KEYWORD)
PG_KEYWORD("execute", K_EXECUTE, RESERVED_KEYWORD)
PG_KEYWORD("for", K_FOR, RESERVED_KEYWORD)
PG_KEYWORD("foreach", K_FOREACH, RESERVED_KEYWORD)
PG_KEYWORD("from", K_FROM, RESERVED_KEYWORD)
PG_KEYWORD("if", K_IF, RESERVED_KEYWORD)
PG_KEYWORD("in", K_IN, RESERVED_KEYWORD)
PG_KEYWORD("into", K_INTO, RESERVED_KEYWORD)
PG_KEYWORD("loop", K_LOOP, RESERVED_KEYWORD)
PG_KEYWORD("not", K_NOT, RESERVED_KEYWORD)
PG_KEYWORD("null", K_NULL, RESERVED_KEYWORD)
PG_KEYWORD("or", K_OR, RESERVED_KEYWORD)
PG_KEYWORD("strict", K_STRICT, RESERVED_KEYWORD)
PG_KEYWORD("then", K_THEN, RESERVED_KEYWORD)
PG_KEYWORD("to", K_TO, RESERVED_KEYWORD)
PG_KEYWORD("using", K_USING, RESERVED_KEYWORD)
PG_KEYWORD("when", K_WHEN, RESERVED_KEYWORD)
PG_KEYWORD("while", K_WHILE, RESERVED_KEYWORD)
};
...........
static const ScanKeyword unreserved_keywords[] = {
PG_KEYWORD("absolute", K_ABSOLUTE, UNRESERVED_KEYWORD)
PG_KEYWORD("alias", K_ALIAS, UNRESERVED_KEYWORD)
PG_KEYWORD("array", K_ARRAY, UNRESERVED_KEYWORD)
PG_KEYWORD("assert", K_ASSERT, UNRESERVED_KEYWORD)
PG_KEYWORD("backward", K_BACKWARD, UNRESERVED_KEYWORD)
PG_KEYWORD("close", K_CLOSE, UNRESERVED_KEYWORD)
PG_KEYWORD("collate", K_COLLATE, UNRESERVED_KEYWORD)
PG_KEYWORD("column", K_COLUMN, UNRESERVED_KEYWORD)
PG_KEYWORD("column_name", K_COLUMN_NAME, UNRESERVED_KEYWORD)
PG_KEYWORD("constant", K_CONSTANT, UNRESERVED_KEYWORD)
PG_KEYWORD("constraint", K_CONSTRAINT, UNRESERVED_KEYWORD)
PG_KEYWORD("constraint_name", K_CONSTRAINT_NAME, UNRESERVED_KEYWORD)
PG_KEYWORD("continue", K_CONTINUE, UNRESERVED_KEYWORD)
PG_KEYWORD("current", K_CURRENT, UNRESERVED_KEYWORD)
PG_KEYWORD("cursor", K_CURSOR, UNRESERVED_KEYWORD)
PG_KEYWORD("datatype", K_DATATYPE, UNRESERVED_KEYWORD)
PG_KEYWORD("debug", K_DEBUG, UNRESERVED_KEYWORD)
PG_KEYWORD("default", K_DEFAULT, UNRESERVED_KEYWORD)
PG_KEYWORD("detail", K_DETAIL, UNRESERVED_KEYWORD)
PG_KEYWORD("diagnostics", K_DIAGNOSTICS, UNRESERVED_KEYWORD)
PG_KEYWORD("dump", K_DUMP, UNRESERVED_KEYWORD)
PG_KEYWORD("elseif", K_ELSIF, UNRESERVED_KEYWORD)
PG_KEYWORD("elsif", K_ELSIF, UNRESERVED_KEYWORD)
PG_KEYWORD("errcode", K_ERRCODE, UNRESERVED_KEYWORD)
PG_KEYWORD("error", K_ERROR, UNRESERVED_KEYWORD)
PG_KEYWORD("exception", K_EXCEPTION, UNRESERVED_KEYWORD)
PG_KEYWORD("exit", K_EXIT, UNRESERVED_KEYWORD)
PG_KEYWORD("fetch", K_FETCH, UNRESERVED_KEYWORD)
PG_KEYWORD("first", K_FIRST, UNRESERVED_KEYWORD)
PG_KEYWORD("forward", K_FORWARD, UNRESERVED_KEYWORD)
PG_KEYWORD("get", K_GET, UNRESERVED_KEYWORD)
PG_KEYWORD("hint", K_HINT, UNRESERVED_KEYWORD)
PG_KEYWORD("import", K_IMPORT, UNRESERVED_KEYWORD)
PG_KEYWORD("info", K_INFO, UNRESERVED_KEYWORD)
PG_KEYWORD("insert", K_INSERT, UNRESERVED_KEYWORD)
PG_KEYWORD("is", K_IS, UNRESERVED_KEYWORD)
PG_KEYWORD("last", K_LAST, UNRESERVED_KEYWORD)
PG_KEYWORD("log", K_LOG, UNRESERVED_KEYWORD)
PG_KEYWORD("message", K_MESSAGE, UNRESERVED_KEYWORD)
PG_KEYWORD("message_text", K_MESSAGE_TEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("move", K_MOVE, UNRESERVED_KEYWORD)
PG_KEYWORD("next", K_NEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("no", K_NO, UNRESERVED_KEYWORD)
PG_KEYWORD("notice", K_NOTICE, UNRESERVED_KEYWORD)
PG_KEYWORD("open", K_OPEN, UNRESERVED_KEYWORD)
PG_KEYWORD("option", K_OPTION, UNRESERVED_KEYWORD)
PG_KEYWORD("perform", K_PERFORM, UNRESERVED_KEYWORD)
PG_KEYWORD("pg_context", K_PG_CONTEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("pg_datatype_name", K_PG_DATATYPE_NAME, UNRESERVED_KEYWORD)
PG_KEYWORD("pg_exception_context", K_PG_EXCEPTION_CONTEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("pg_exception_detail", K_PG_EXCEPTION_DETAIL, UNRESERVED_KEYWORD)
PG_KEYWORD("pg_exception_hint", K_PG_EXCEPTION_HINT, UNRESERVED_KEYWORD)
PG_KEYWORD("print_strict_params", K_PRINT_STRICT_PARAMS, UNRESERVED_KEYWORD)
PG_KEYWORD("prior", K_PRIOR, UNRESERVED_KEYWORD)
PG_KEYWORD("query", K_QUERY, UNRESERVED_KEYWORD)
PG_KEYWORD("raise", K_RAISE, UNRESERVED_KEYWORD)
PG_KEYWORD("relative", K_RELATIVE, UNRESERVED_KEYWORD)
PG_KEYWORD("result_oid", K_RESULT_OID, UNRESERVED_KEYWORD)
PG_KEYWORD("return", K_RETURN, UNRESERVED_KEYWORD)
PG_KEYWORD("returned_sqlstate", K_RETURNED_SQLSTATE, UNRESERVED_KEYWORD)
PG_KEYWORD("reverse", K_REVERSE, UNRESERVED_KEYWORD)
PG_KEYWORD("row_count", K_ROW_COUNT, UNRESERVED_KEYWORD)
PG_KEYWORD("rowtype", K_ROWTYPE, UNRESERVED_KEYWORD)
PG_KEYWORD("schema", K_SCHEMA, UNRESERVED_KEYWORD)
PG_KEYWORD("schema_name", K_SCHEMA_NAME, UNRESERVED_KEYWORD)
PG_KEYWORD("scroll", K_SCROLL, UNRESERVED_KEYWORD)
PG_KEYWORD("slice", K_SLICE, UNRESERVED_KEYWORD)
PG_KEYWORD("sqlstate", K_SQLSTATE, UNRESERVED_KEYWORD)
PG_KEYWORD("stacked", K_STACKED, UNRESERVED_KEYWORD)
PG_KEYWORD("table", K_TABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("table_name", K_TABLE_NAME, UNRESERVED_KEYWORD)
PG_KEYWORD("type", K_TYPE, UNRESERVED_KEYWORD)
PG_KEYWORD("use_column", K_USE_COLUMN, UNRESERVED_KEYWORD)
PG_KEYWORD("use_variable", K_USE_VARIABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("variable_conflict", K_VARIABLE_CONFLICT, UNRESERVED_KEYWORD)
PG_KEYWORD("warning", K_WARNING, UNRESERVED_KEYWORD)
};
```
如何强制使用保留字keyword
对于identity，可以使用双引号，强制使用关键字。
```
postgres=# create table "cast" (id int);
CREATE TABLE
postgres=# select * from "cast";
id
(0 rows)
```
参考
《PostgreSQL Oracle 兼容性之 - 系统列(关键字、保留字)的处理(ctid, oid, cmin, cmax, xmin, xmax)》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
