cannot change data type of view column SQL

Question

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

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;

and I use the returned value in a view called 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

If I cast(findIntl(s.id) as numeric(4,2) it says

ERROR: cannot change data type of view column "percent" from integer to numeric(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.

Also, if I remove semester from findPercent and just keep percent there and select findIntl(s.id) only then it says

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?

Answer 1

I think the Postgres documentation is pretty clear on the use of replace for views:

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.

You need to drop the view and re-create it.

Answer 2

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.

drop view findPercent;

Change the findIntl function and then

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;