如何在视图中添加ROW_NUMBER()函数？

Question

在PostgreSQL 8.4中，我想创建一个带有id的三个表的视图。因此，我想在我的视图中拥有以下结构：

num serial,
name_dispatcher character varying(250)
the_geom geometry

我可以从表中选择name_dispatcher和the_geom。

 CREATE VIEW lineView
      AS SELECT 'name' AS name_dispatcher, the_geom
      FROM line1
      UNION
      SELECT 'name' AS name_dispatcher, the_geom
      FROM line2
      UNION
      SELECT 'name' AS name_dispatcher, the_geom
      FROM line3

如何在视图中创建num列？

更新

我找到了一个解决方案：

ROW_NUMBER() OVER(ORDER BY lineView.voltage)

但是我不知道如何在ALTER VIEW中使用它。我该如何将其放入其中？

- Kliver Max

---

顺便提一下：要注意UNION操作可能会很耗费资源，因为它会消除重复项。如果你确定最终结果中不会有重复项，或者你可以接受存在重复项的情况，那么切换到UNION ALL可能会更有益处。 - Damien_The_Unbeliever

Answer 1

你不能使用 ALTER VIEW 去删除或添加列。我引用 ALTER VIEW 手册中的内容：

ALTER VIEW 可以改变视图的各种辅助属性。(如果你想修改视图定义的查询，使用 CREATE OR REPLACE VIEW。)

但是仅仅使用 CREATE OR REPLACE VIEW 是不够的。另一个引用自手册的说明：

新查询必须生成与现有视图查询生成的相同列。

所以要使用 DROP 和 CREATE 命令来替换视图：

DROP VIEW lineview;

CREATE VIEW lineview AS
SELECT *, row_number() OVER(ORDER BY ???) AS num
FROM (
   SELECT 'name' AS name_dispatcher, the_geom
   FROM line1

   UNION
   SELECT 'name' AS name_dispatcher, the_geom
   FROM line2

   UNION
   SELECT 'name' AS name_dispatcher, the_geom
   FROM line3
   ) x

我使用子查询，因为我假设您想要将row_number()添加到所有行中。在这方面上，您的问题不太明确。
如果您只想要一个无序的唯一标识符，请使用row_number() OVER()。

- Erwin Brandstetter

---

@KliverMax：这是“AS x”的缩写，是子查询的表别名。SQL需要一个。 - Erwin Brandstetter