Randall Perry <rgp@systame.com> writes:
> Here's my view definition:
> View "v_cust_rev_by_month"
> Column | Type | Modifiers
> ---------------+-----------------------+-----------
> id | integer |
> cust_code | character varying(25) |
> date | date |
> month | text |
> total_uploads | integer |
> revenues | numeric |
> balance | numeric |
> In testing, if I create a new table from this view and don't include the
> numeric fields, it links to access ok.
> If include either of the numeric fields I get the 'field is too long' error.
> If I link the original table the view is derived from (the view is 3 steps
> removed from the original because it calls on views that call on views) I
> don't get the error.
My bet is that something on the client side is getting confused by the
lack of precision specification for the numeric columns in the view. It
works on the original table because that has a precision spec.
You can probably work around this by redefining the view with explicit
casts. Note the difference in the following examples:
regression=# create table foo(f1 numeric(7,2));
CREATE
regression=# create view v as select f1, f1+1 from foo;
CREATE
regression=# create view v2 as select f1, (f1+1)::numeric(7,2) from foo;
CREATE
regression=# \d v
View "v"
Column | Type | Modifiers
----------+--------------+-----------
f1 | numeric(7,2) |
?column? | numeric |
View definition: SELECT foo.f1, (foo.f1 + '1'::"numeric") FROM foo;
regression=# \d v2
View "v2"
Column | Type | Modifiers
---------+--------------+-----------
f1 | numeric(7,2) |
numeric | numeric(7,2) |
View definition: SELECT foo.f1, ((foo.f1 + '1'::"numeric"))::numeric(7,2) AS "numeric" FROM foo;
In the longer run it might be possible to tweak the ODBC driver to
prevent this failure --- I'm not sure what ODBC does when it sees a
-1 typmod for a numeric column, but perhaps it could do something
different than it does now.
regards, tom lane