postgresql 无法更改视图列 SQL 的数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36790981/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
cannot change data type of view column SQL
提问by Najji Muhammed
I keep getting this error
我不断收到此错误
cannot change data type of view column "percent" from integer to double precision
无法将视图列“百分比”的数据类型从整数更改为双精度
I have a function called findIntl()which returns a float
我有一个函数调用findIntl()它返回一个浮点数
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 = ;
$$ language sql;
and I use the returned value in a view called findPercent
我在名为 findPercent 的视图中使用返回值
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;
If I replace findIntl()with another column of values it works perfectly fine but when findIntl()is there it says cannot change data type of view column "percent" from integer to double precision
如果我findIntl()用另一列值替换它工作得很好但是findIntl()它什么时候说cannot change data type of view column "percent" from integer to double precision
If I cast(findIntl(s.id) as numeric(4,2)it says
如果我cast(findIntl(s.id) as numeric(4,2)说
ERROR: cannot change data type of view column "percent" from integer to numeric(4,2)
错误：无法将视图列“百分比”的数据类型从整数更改为数字 (4,2)
I have read on stackoverflow about something to do with dropping tables but I don't quite understand why I must do that, and I am not even sure if it applies to this case.
我在 stackoverflow 上读过关于删除表的内容，但我不太明白为什么我必须这样做，我什至不确定它是否适用于这种情况。
Also, if I remove semester from findPercent and just keep percent there and select findIntl(s.id)only then it says
另外，如果我从 findPercent 中删除学期并只保留百分比，select findIntl(s.id)然后它才会说
ERROR: cannot drop columns from view
错误：无法从视图中删除列
I'm new to SQL so sorry for my ignorance but when I use a column of integers it works perfectly fine but when I use a function to return values why does this break and how can I fix this?
我是 SQL 新手，很抱歉我的无知，但是当我使用一列整数时，它工作得很好，但是当我使用函数返回值时，为什么会中断，我该如何解决？
回答by Gordon Linoff
I think the Postgres documentationis pretty clear on the use of replace for views:
我认为 Postgres文档对视图的替换使用非常清楚：
CREATE OR REPLACE VIEWis 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.
CREATE OR REPLACE VIEW是相似的，但如果同名视图已经存在，则替换它。新查询必须生成与现有视图查询生成的列相同的列（即，相同的列名以相同的顺序和相同的数据类型），但它可能会在列表的末尾添加额外的列。产生输出列的计算可能完全不同。
You need to drop the view and re-create it.
您需要删除视图并重新创建它。
回答by Allan Girao
Views are aliases to querys. Dropping them won't affect your data, so don't worry dropping them. To work, you have to drop and create it again when you change column type in views on postgres.
视图是查询的别名。删除它们不会影响您的数据，所以不要担心删除它们。要工作，您必须在更改 postgres 视图中的列类型时删除并再次创建它。
drop view findPercent;
Change the findIntl function and then
更改 findIntl 函数，然后
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;
