PostgreSQL - 修改扩展中的 CREATE TABLE 语法

Question

是否有可能编写一个PostgreSQL扩展来修改DDL语法？

我正在创建一个基于PostgreSQL和PostGIS的扩展，以支持特定空间数据模型（OMT-G）的完整性约束。为此，我想修改CREATE TABLE语法，它接受这种语法的CONSTRAINT：

CONSTRAINT constraint_name CHECK ( expression )

但我希望创建自己的语法，类似下面的示例，然后调用已经编写好的函数或触发器。

CREATE TABLE school_district (
      id integer PRIMARY KEY,
      school_name varchar(120)
      geom geometry,

      SPATIAL_CONSTRAINT PLANAR_SUBDIVISION (geom),
      SPATIAL_CONSTRAINT CONTAINS school (geom)**
);

这是可能的吗？如果是，怎么做呢？

- lizardo

---

不，那是不可能的。但是如果您提供一个真实的例子来说明您想要实现什么，可能会有一些替代方案。 - Patrick

只需使用 CHECK 语法即可。这样就可以实现您想要的功能。 - Gordon Linoff

仍然不可能。如果您想扩展语法，必须修改源代码并直接在C中编写功能。这可能不是一个非常可行的想法... 如果您有许多重复的约束条件并希望简化流程，请查看domains。 - Patrick

谢谢@Patrick。这些域名似乎很有用。但是以防万一，我想修改源代码来更改语法。你建议我做什么？你知道任何教程或提示可以给我吗？我曾经检查过源代码，但没有发现任何东西。 - lizardo

我不知道有关于PG编码的任何教程。在网站上有一些资源可以帮助你入门。黑客邮件列表可能是一个寻找答案的好地方，还有一个专门用于使用PG和编码入门的邮件列表（忘记名字了）。最好做好充分的准备，因为你不仅需要编写所需的特定功能，还需要编辑解析器规则，可能要处理规划器（如何将你的添加集成到查询中）以及其他一系列事情。 - Patrick

Answer 1

正如其他人所评论的那样，无法通过扩展来修改Postgres语法。在黑客邮件列表中有一些相关讨论，但没有人看到任何可行的方法使Bison语法可扩展。

你提出的另一个问题是CHECK约束（似乎这就是你在这里尝试做的）不能安全地引用其他表。

听起来你真正想要的是可扩展的外键支持。社区也希望有这样的功能，至少对于数组而言。这个想法是支持类似int[]这样的东西，其中每个元素都应该被视为对另一个表的外键引用。你描述的与此类似：你想使用不同的操作符而不是不同的数据类型。

不过现在，我认为你能做的最好的事情就是为用户提供一个函数，在表上放置适当的列和触发器。该列将是指向学校表的外键。触发器将找到一个合适的学校，并在新列中填充它的主键。（你可能还需要在学校表上添加一个BEFORE DELETE触发器来处理删除学校的情况。）

你需要外键字段的原因是外键触发器具有不同的可见性规则，因此你无法完全在用户空间中模拟它们。如果你不是非常谨慎，你可以只有一个AFTER INSERT触发器，它会查找学校并在找不到学校时抛出错误。

- Jim Nasby

---

谢谢@Jim。实际上，由于数据是空间数据，所以不是外键。两个表之间的关系可以只是空间关系（检查一个元素是否在另一个元素的边界内），而无需使用外键。但还是谢谢。我会想另一种方法使这些约束更简单易用。 - lizardo

Answer 2

就纯DDL解决方案而言，最有前途的方法是使用CREATE TABLE ... CONSTRAINT ... EXCLUDE，它在单个表上运行GiST，并且只有相对有限的运算符仅适用于边界框（例如&&）。

CREATE TABLE polygons (
    geom geometry(Polygon,4326),
    EXCLUDE USING gist (geom WITH &&)
);
INSERT INTO polygons(geom)
VALUES('SRID=4326;POLYGON ((0 0, 0 1, 1 0, 0 0))');

但是，这会产生冲突（即使几何图形实际上并没有重叠）：

INSERT INTO polygons(geom)
VALUES('SRID=4326;POLYGON ((1 1, 1 2, 2 2, 2 1, 1 1))');

错误：冲突的键值违反了排除约束“polygons_geom_excl” 详细信息：键（geom）=（0103000020E61000000100000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F）与现有键（geom）=（0103000020E61000000100000004000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000000000000000000000000000000000000000）冲突。

---

如@Jim所述，构建一个表的约束条件时最好的方法是创建一个好的触发器函数，并在两个表上使用它。通常应该用PL/pgSQL编写它，您可以嵌入有用的消息，例如：

RAISE EXCEPTION 'School % is not located in school district % (it is % away)',
                s.name, d.name, ST_Distance(s.geom, d.geom);

这样，如果您编辑了school_district或schools表中的任何一个，触发器将在UPDATE、INSERT或DELETE时进行检查，以确定条件是否仍然有效。

- Mike T