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=(SELECT oid
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
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
