Thread: 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