I was just shown the following example:
CREATE TABLE profile (haushaltseinkommen_pm numeric(22,2));
CREATE VIEW profile_view AS
SELECT *, haushaltseinkommen_pm*12 AS haushaltseinkommen_pa FROM profile;
7.0.* pg_dump produces the following for the view:
CREATE TABLE "profile_view" ( "haushaltseinkommen_pm" numeric(22,2), "haushaltseinkommen_pa" numeric
);
CREATE RULE "_RETprofile_view" AS ON SELECT TO profile_view DO INSTEAD SELECT profile.haushaltseinkommen_pm,
(profile.haushaltseinkommen_pm* '12'::"numeric") AS haushaltseinkommen_pa FROM profile;
AFAICS this is perfectly legitimate, but both 7.0.* and current backends
will reject the CREATE RULE with
ERROR: select rule's target entry 2 has different size from attribute haushaltseinkommen_pa
The problem here is that DefineQueryRewrite checks
if (attr->atttypmod != resdom->restypmod) elog(ERROR, "select rule's target entry %d has
differentsize from attribute %s", i, attname);
where attr will have the default precision/scale for NUMERIC, as set up
by the CREATE TABLE, but resdom will have -1 because that's what you're
going to get from a numeric expression. (In the CREATE VIEW case, they
both have -1, evidently because CREATE VIEW doesn't force a default
NUMERIC precision to be inserted in the table definition. Not sure if
that's OK or not.)
I think we'd better fix this, else we will have problems reading 7.0
dump files. I can see two possible answers:
1. Remove this check entirely.
2. Allow the typmods to be different if one of them is -1.
I'm not entirely sure which way to jump. The former seems simpler but
might perhaps allow creation of bogus views --- any opinions?
regards, tom lane