postgresql - postgres 将 View 列的数据类型从未知更改为文本

Question

我只是新建了一个view如下

CREATE OR REPLACE VIEW gettreelistvw AS 
 SELECT "CRM".groupid, 'pointrewarding'::text AS applicationid, "CM".menuid, "CM".menuname, "CM".levelstructure, "CM".moduleid, "CM".haschild, "CM".installed
   FROM core_capabilitymap "CRM"
   JOIN core_menus "CM" ON "CRM".menuid::text = "CM".menuid::text;

ALTER TABLE gettreelistvw

当我执行这个错误出现

ERROR: cannot change data type of view column "applicationid" from unknown to text

尽管我已经将 applicationid 列的值转换为文本。它仍然被识别为未知数据类型

'pointrewarding'::text

postgres 转换的替代方法也不起作用。

CAST('pointrewarding' AS TEXT)

如何解决这个问题。

Answer 1

如果要更改 View 列的数据类型，则必须先删除它，然后再创建它。

Version 9.2 docs

CREATE OR REPLACE VIEW .... The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list.

添加了强调。