Обсуждение: Underlying view columns?
This one is quite long, but I guess the quality of the answer depends on the quality of the question :). I use views to simplify the underlying database schema for the end users. The end do however still like to know about relations. Here is a very simple example: CREATE TABLE "t_orgs" ( "ID" SERIAL PRIMARY KEY, "name" varchar(32) NOT NULL ); CREATE TABLE "t_ppl" ( "ID" SERIAL PRIMARY KEY, "org" integer REFERENCES "t_orgs" ("ID"), "name" varchar(48) ); CREATE VIEW "organisations" AS SELECT "ID", "name" FROM "t_orgs"; CREATE VIEW "people" AS SELECT "ID", "org" AS "organisation", "name" AS "fullname" FROM "t_ppl"; And to this some rules and added defaults on the views to make them updateable in a nice fashion. But now the problem; the "organisation" column of the "people" view above is implicitly referencing the "organisations" view, as the underlying tables have this constraint. And I want the end user to be able to know about this. So I created a type and a function to query for this info such as: CREATE TYPE tableinfo_ret AS ( "column" name, "default" text, "notnull" boolean, "references" name ); CREATE FUNCTION tableinfo(a_table name) RETURNS SETOF tableinfo_ret AS $$ ... $$ LANGUAGE plpgsql STABLE SECURITY DEFINER; In my first attempt I depend on tables and views having the same names for columns to get it working, an ugly solution. It work for 9 of 10 cases but fails miserably for the rest. So I thought that maybe pg_depend could be used, after all a view is depending on the table it fetches data from. So I did some testing, for example: SELECT *, (SELECT relname FROM pg_class WHERE oid=refobjid LIMIT 1) FROM pg_depend WHERE objid=(SELECToid FROM pg_class WHERE relname='people') AND deptype='n'; I find the result somewhat confusing. I get only one row as result, maybe this is fine as a view perhaps does not have dependencies for each of it's columns, but only one as a whole? But the fetched refobjid is not in pg_class, so surely not the underlying table? I do the select on the objid as the documentation specify this as the "dependent" object, and I interpret this as the view is dependent on the underlying table. This is not quite true in my opinion as one should be able to drop columns not used by the view in the underlying table. So there should be one row for each referenced column, should there not? If I swap objid for refobjid I do get more results, but none where objid and refobjid references to the table and view in any combination. Have I misunderstood the concept of pg_depend? Can it even be used for what I intend, and if not in what direction should I be searching next? Regards -- //Fredrik Olsson Treyst AB +46-19-362182 fredrik.olsson@treyst.se
Fredrik Olsson wrote: > Have I misunderstood the concept of pg_depend? Can it even be used for > what I intend, and if not in what direction should I be searching next? I'd take a few minutes to investigate the new-sys-views project and see if they've got anything you can steal: http://pgfoundry.org/projects/newsysviews -- Richard Huxton Archonet Ltd
Fredrik Olsson <fredrik.olsson@treyst.se> writes: > Have I misunderstood the concept of pg_depend? Can it even be used for > what I intend, and if not in what direction should I be searching next? What you missed is that the per-column dependencies you are looking for go from the view's rewrite rule to the underlying table. Here's an example in CVS tip: regression=# create table foo (f1 int, f2 text); CREATE TABLE regression=# create view bar as select * from foo; CREATE VIEW regression=# select classid::regclass,objid,objsubid,refclassid::regclass,refobjid,refobjsubid,deptype from pg_depend whererefobjid in ('foo'::regclass,'bar'::regclass); classid | objid | objsubid | refclassid | refobjid | refobjsubid |deptype ------------+-------+----------+------------+----------+-------------+---------pg_type | 62950 | 0 | pg_class | 62949 | 0 | ipg_class | 62951 | 0 | pg_class | 62949 | 0 | ipg_type | 62955 | 0 | pg_class | 62954 | 0 | ipg_rewrite | 62956 | 0 | pg_class | 62954 | 0 |ipg_rewrite | 62956 | 0 | pg_class | 62949 | 1 | npg_rewrite | 62956 | 0 | pg_class | 62949 | 2 | npg_rewrite | 62956 | 0 | pg_class | 62954 | 0 | n (7 rows) What we have there is: * implicit dependency of foo's rowtype on foo. * implicit dependency of foo's toast table on foo. * implicit dependency of bar's rowtype on bar. * implicit dependency of bar's ON SELECT rewrite rule on bar. * normal dependency of bar's rewrite rule on foo.f1 (refobjsubid is the column number). * normal dependency of bar's rewrite rule on foo.f2. * normal dependency of bar's rewrite rule on foo as a whole. That last dependency comes from the appearance of foo in bar's FROM list, while the per-column dependencies come from the individual column references in the SELECT output list. There isn't anything in pg_depend that would let you associate particular columns of bar's output with particular dependencies, so I'm not sure it really will help for your problem. I don't think there's any way you could find that out except by parsing the stored rule expression, which I would strongly NOT recommend, as your code will inevitably break every time we modify expression trees (which is often). regards, tom lane
Tom Lane wrote: > Fredrik Olsson <fredrik.olsson@treyst.se> writes: > >> Have I misunderstood the concept of pg_depend? Can it even be used for >> what I intend, and if not in what direction should I be searching next? >> > > What you missed is that the per-column dependencies you are looking for > go from the view's rewrite rule to the underlying table. > <snip> > There isn't anything in pg_depend that would let you associate > particular columns of bar's output with particular dependencies, so I'm > not sure it really will help for your problem. I don't think there's > any way you could find that out except by parsing the stored rule > expression, which I would strongly NOT recommend, as your code will > inevitably break every time we modify expression trees (which is often). > > regards, tom lane > Thanks for the good explanation. I resort to keep depending on name-similarities, with added exceptions for legacy objects. That and some well documented guidelines for this projects future additions solves the problem, in a way that looks good to the end user at least :). -- //Fredrik Olsson Treyst AB +46-19-362182 fredrik.olsson@treyst.se