Re: where's the reference to a view, here? - Mailing list pgsql-general

On Fri, Jun 22, 2001 at 09:30:05AM +0100, Richard Huxton wrote:
> From: "will trillich" <will@serensoft.com>
> > inv=# \d inv
> >                  View "inv"
> >  Attribute |         Type         | Modifier
> > -----------+----------------------+----------
> [snip]
> >  amt       | numeric(65535,65531) |
>
> Is this large a numeric deliberate, or has something got mangled here?

i noticed that, too. (wasn't me, wasn't me!)

simple view created thus:

    CREATE VIEW inv AS
    SELECT b.client, b.id, b.code, b.rundate, b.job, b.invdate, b.costs, b.total,
        l.item, l.hrs, l.rate, l.other, l.descr,
        CASE
            WHEN (l.rate ISNULL)
            THEN l.other
            ELSE (l.rate * l.hrs)
        END
        AS amt
    FROM "_inv" b,
        "_invitem" l
    WHERE (b.id = l.id);

notice how 'pg_dump' shows the phantom table behind the view:

    CREATE TABLE "inv" (
        "client" character varying(8),
        "id" int4,
        "code" character varying(20),
        "rundate" date,
        "job" character varying(6),
        "invdate" date,
        "costs" bool,
        "total" numeric(7,2),
        "item" int2,
        "hrs" numeric(4,1),
        "rate" numeric(6,2),
        "other" numeric(7,2),
        "descr" character varying(80),
        "amt" numeric -- <<=== no big whoop, there
    );

but "\d inv" shows the oddness on the calculated field:

                     View "inv"
     Attribute |         Type         | Modifier
    -----------+----------------------+----------
     client    | varchar(8)           |
     id        | integer              |
     code      | varchar(20)          |
     rundate   | date                 |
     job       | varchar(6)           |
     invdate   | date                 |
     costs     | boolean              |
     total     | numeric(7,2)         |
     item      | smallint             |
     hrs       | numeric(4,1)         |
     rate      | numeric(6,2)         |
     other     | numeric(7,2)         |
     descr     | varchar(80)          |
     amt       | numeric(65535,65531) | <<=== say what? <<===
    View definition: SELECT b.client, b.id, b.code, b.rundate, b.job, b.invdate, b.costs, b.total, l.item, l.hrs,
l.rate,l.other, l.descr, CASE WHEN (l.rate ISNULL) THEN l.other ELSE (l.rate * l.hrs) END AS amt FROM "_inv" b,
"_invitem"l WHERE (b.id = l.id); 

curiouser and curiouser. (this probably reflects an
internal-type flag situation, i'd bet. but it's still spooky to
look at. just think, 65500 digits of precision eating away at my
hard disk... not :)

the rest is working much better, now that i've done a
dump/reload of the schema and data.

--
I figure: if a man's gonna gamble, may as well do it
without plowing.   -- Bama Dillert, "Some Came Running"

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!