sql - 无法更改 View 列 SQL 的数据类型

Question

我一直收到这个错误

cannot change data type of view column "percent" from integer to double precision

我有一个名为 findIntl() 的函数，它返回一个 float

create or replace function findIntl(integer) returns float as $$
  select cast(count(*) as float)
  from students s
  join program_enrolments pe on (s.id = pe.student)
  where s.stype = 'intl' and pe.semester = $1;
$$ language sql;

我在名为 findPercent 的 View 中使用返回值

create or replace view findPercent(semester, percent) as
  select q6(s.id), findIntl(s.id)
  from semesters s
  where s.id = id and s.term ~ 'S' and s.year >= 2004;

如果我用另一列值替换 findIntl() 它工作得很好但是当 findIntl() 在那里时它说 cannot change data type of view从整数到 double 的“百分比”列

如果我 cast(findIntl(s.id) as numeric(4,2) 它说

ERROR: cannot change data type of view column "percent" from integer to numeric(4,2)

我在 stackoverflow 上读到了有关删除表的内容，但我不太明白为什么我必须这样做，而且我什至不确定它是否适用于这种情况。

此外，如果我从 findPercent 中删除 semester 并只保留 percent 并 select findIntl(s.id) 然后它会显示

ERROR: cannot drop columns from view

我是 SQL 的新手，很抱歉我的无知，但是当我使用一列整数时它工作得很好但是当我使用函数返回值时为什么会中断，我该如何解决？

Answer 1

我认为 Postgres documentation对于 View 的替换使用非常清楚:

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. 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. The calculations giving rise to the output columns may be completely different.

您需要删除 View 并重新创建它。