Обсуждение: hot to determine calculated fields (views)

the lights are beginning to glow. :)

those pg_* tables are really something! but how can i tell if an
attribute of a relation (field of a table) is calculated via a
view, or actual data from a table?

i'm trying to use the pg_* system tables in some views that'll
help my app determine various aspects of fields in the database,
such as how much of a limit to impose on data entry (varchar(20)
should get <input type="text" maxlength="20" ...> for example).

    create view sys_field_size as
    SELECT
        c.relname   as class,
        a.attname   as field,
        a.attlen    as storage,
        a.attnum    as field_no,
        a.atttypmod as field_mod,
        CASE
            WHEN a.atttypmod<=0
                THEN CASE
                    WHEN a.attlen<0
                        THEN -1
                    ELSE
                        NULL
                END
            ELSE
                a.atttypmod - 4
        END
            AS SIZE
    FROM
        pg_attribute a
            JOIN
        pg_class c
                ON c.oid = a.attrelid
    WHERE
        a.attnum > 0 -- only user-defined fields, thanks
        AND
        c.relname !~ '^pg_' -- not a postgres system table
        AND
        c.relkind IN ('v','r') -- view or relation/table
    ;

well, it's a start.

i've got tables (_name) and related views (name) where the views
do some munging to the fields for display, or they add new fields
based on the actual data in the table. for html-generation, i
don't want the calculated fields looking like data entry is
possible, so i need to distinguish them from the editable ones.

QUESTION:

how can i tell whether i'm looking at a computed field (from a
view) as opposed to actual data (brought in directly from a
table)? something in pg_attribute, i hope... :)

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

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

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

will trillich <will@serensoft.com> writes:
> how can i tell whether i'm looking at a computed field (from a
> view) as opposed to actual data (brought in directly from a
> table)? something in pg_attribute, i hope... :)

You mean, how to tell which columns of a view are just "select foo from..."
and which are more complex expressions?

AFAICS, the only way is to parse the view definition rule.  The system
won't give you any help on this, because it neither knows nor cares...

            regards, tom lane

On Sat, Jan 25, 2003 at 11:50:42AM -0500, Tom Lane wrote:
> will trillich <will@serensoft.com> writes:
> > how can i tell whether i'm looking at a computed field (from a
> > view) as opposed to actual data (brought in directly from a
> > table)? something in pg_attribute, i hope... :)
>
> You mean, how to tell which columns of a view are just "select foo from..."
> and which are more complex expressions?
>
> AFAICS, the only way is to parse the view definition rule.  The system
> won't give you any help on this, because it neither knows nor cares...

so there's no difference in the defined relation (table) between
these two--

    create table first (
        i int,
        j int,
        t text,
        x text
    );

and

    create view last as
    select
                         i,
        i * j         as j,
                         t,
        substr(x,i,j) as x
    from first
    ;

? no flags at all, stored anywhere?

pooh.

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

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

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !