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/ !