PostgreSQL Oracle兼容性 之 - parser SQL保留 关键字(keywrods)大全

4 minute read

背景

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)》

Flag Counter

digoal’s 大量PostgreSQL文章入口