sql - PostgreSQL:如何检查我没有使用任何关键字？

Question

我如何确保我没有使用任何 SQL and PostgreSQL's key words ，甚至是非保留的，在我的架构标识符中(表名、列名...)？

有没有办法用 SQL 查询来做到这一点？

Answer 1

通过引用并将结果与​​原始结果进行比较，检查是否需要引用列名。

SELECT * from pg_attribute
WHERE attname <> quote_ident(attname)
        ;

CREATE TABLE bad ("integer" integer not null primary key
        , "where" TEXT NOT NULL
        , "table" TEXT NOT NULL
        , "as" TEXT NOT NULL
        , "is" TEXT NOT NULL
        , "primary" TEXT NOT NULL
        , "references" TEXT NOT NULL
        );

SELECT * from pg_attribute
WHERE attname <> quote_ident(attname)

DROP TABLE bad cascade;

上面的代码也会捕获 MixedCaseIdentifiers。要抑制这些，请使用:

CREATE TABLE mixed ("Mixed" integer not null primary key );

SELECT * from pg_attribute
WHERE lower(attname) <> quote_ident(lower(attname))
        ;

DROP TABLE mixed cascade;

但这也会捕获带有嵌入空格的标识符。要捕获这些，请在比较之前删除它们:

CREATE TABLE spaced ("spa ced" integer not null primary key );

SELECT * from pg_attribute
WHERE lower(replace(attname, ' ' ,'') )
   <> quote_ident(lower(replace(attname, ' ' ,'')))
    ;

---

同样的技巧，包装到 SQL 函数中:

CREATE function check_needsquoting( str text ) returns Boolean AS
$func$
select lower(replace(str, ' ' ,'') )
   <> quote_ident(lower(replace(str, ' ' ,'')))
   ;
$func$ LANGUAGE sql;

SELECT check_needsquoting ( 'FooBar' );
SELECT check_needsquoting ( 'where' );
SELECT check_needsquoting ( 'create' );

DROP function check_needsquoting( str text );

结果:

CREATE FUNCTION
 check_needsquoting 
--------------------
 f
(1 row)

 check_needsquoting 
--------------------
 t
(1 row)

 check_needsquoting 
--------------------
 t
(1 row)

DROP FUNCTION

---

将此函数与@vyegorov 提到的 pg_get_keywords(); 函数的结果相结合得到:

SELECT
        kw.word, kw.catcode
        , check_needsquoting(kw.word) AS needsquote
from pg_get_keywords() kw
ORDER BY kw.word
        ;

得出的结论是只需要引用catcode IN ( 'C', 'R' )。注意:pg_get_keywords() 似乎从 Postgresql-8.4 开始可用。 (和 quote_ident() 来自至少 Postgresql-7.2)

---

更新:看起来所有语法中使用的单词都需要检测，而不仅仅是保留的单词:

CREATE function check_ifsyntaxword( str text ) returns Boolean AS
$func$
select EXISTS(
        select 1
        from pg_get_keywords() kw
        WHERE lower(kw.word) = lower( str )
        )
   ;
$func$ LANGUAGE sql;