Underlying view columns? - Mailing list pgsql-hackers

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