> Perhaps the ALTER command could include an optional clause "TRANSFORM
> expr-on-oldcol" to do this. In that case, defaulting to assignment
> coercion would be fine with me.
This would certainly be interesting and shouldn't take too much to
accomplish for simple expressions. If we allow the full expression set
(subselects, multiple columns, etc.) it could get tricky.
> > In order to correct the Var nodes in Check constraints, views,
> > etc. I need to change the varattno and type information, then
> > wrap it in a cast to bring it back to the old data type?
>
> I think a more likely way of making the conversion is to deparse the
> constraint expression to text, then reparse using the new column
> definition. For example, if you were promoting an integer column to
> numeric, and you had a constraint "x > 0", you'd probably be pretty
> surprised if the constraint became "x::int > 0". I think you'd want it
> interpreted as numeric "x > 0".
What about the cases where implicit coercion would break the expression
due to type mismatch, but a cast wouldn't?
If they supply the transform clause we would want to inject the opposite
of it into the expression. Of course, that isn't practical, but do we
allow them to provide a DETRANSFORM clause?
Column is boolean.
ALTER TABLE ... COLUMN col TYPE text TRANSFORM CASE WHEN col IS TRUE THEN 'STATE1' ELSE 'STATE2'
END DETRANSFORM CASE WHEN col = 'STATE1' THEN TRUE ELSE FALSE END;
This would do a data conversion to text, and inject the DETRANSFORM case
statement into expressions (like check and default) allowing their use
to continue?
For a first cut, I'll do simple deparse / reparse and allow a simple
TRANSFORM expression (no subselects, only the current column). The most
useful case at the moment is foreign key mismatch and conversion of
default datatypes into domains.
Malformed (after deparse / reparse) check constraints would reject the
transformation.
> Not sure to what extent we can apply that theory to views, though.
> If it would result in a change in a view's output column datatype,
> do we want to allow that (which would mean recursively propagating
> that column type change to yet other places)?
It would be nice to be able to do this at some point, but I'll leave
views and other complex objects as unsupported (throw an error) when
they attempt.
