From: Kyle Bateman <kyle@actarg.com>
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us
Subject: information_schema problem
Date: Fri, 28 Oct 2005 17:06:35 -0600
Message-ID: <4362AEFB.4040603@actarg.com> (raw)
I'm trying to use information_schema.view_column_usage to determine the
native table from which various view columns descend. This is so my
interface can automatically generate the correct foreign key links from
one view to another.
But in the case where a view references two tables linked by a foreign
key, the key fields are reported as belonging to both tables.
The enclosed example shows two tables related by a foreign key relationship.
The view "event_remind_v" really only produces fields from
event_remind. But
in order to satisfy the "exists" clause, it also references fields from the
event table.
view_column_usage reports the fields "own_id" and "seq" as belonging to
both table "event_remind" and "event". My code needs a way to know that
"event_remind" is the table they "really" come from.
1. Is this the correct behavior for information_schema.view_column_usage?
Should it report a table/column as belonging to more than one table?
If not, how can I fix it?
The enclosed script includes a (slightly revised) version of
view_column_usage that is easier to hack on than the one inside
information_schema.
2. If information_schema.view_column_usage is working right, is there a
way I can modify my local view_column_usage to distinguish between
tables/columns that actually "belong" to the view and related columns
from a foreign key relationship?
Example code:
-----------------------------------------------------------------------------
drop view event_remind_v;
drop table event_remind;
drop table event;
drop view view_column_usage;
-- Contains an entry for each scheduled calendar event
create table event (
own_id int4,
seq int4,
status varchar,
summary varchar,
primary key (own_id,seq)
);
-- Contains an entry for each reminder for each event
create table event_remind (
own_id int4,
seq int4,
advance interval,
primary key (own_id, seq, advance),
foreign key (own_id, seq) references event on update cascade on
delete cascade
);
create view event_remind_v as
select *
from event_remind r
where exists (select * from event where own_id = r.own_id and
seq = r.seq and status = 'open');
;
create view view_column_usage as
select
v.relname as "view_name",
t.relname as "table_name",
at.attname as "column_name"
from pg_depend dv, pg_class v, pg_namespace nv,
pg_depend dt, pg_class t, pg_namespace nt, pg_attribute at
where dv.objid = dt.objid
and dv.refobjid <> dt.refobjid
and dv.deptype = 'i'
and v.relkind = 'v'
and t.relkind IN ('r', 'v')
and v.oid = dv.refobjid
and t.oid = dt.refobjid
and t.relnamespace = nt.oid
and v.relnamespace = nv.oid
and dv.classid = dt.classid and dv.classid =
'pg_catalog.pg_rewrite'::regclass
and dv.refclassid = dt.refclassid and dv.refclassid =
'pg_catalog.pg_class'::regclass
and t.oid = at.attrelid and dt.refobjsubid = at.attnum
and nv.nspname = 'public' and nt.nspname = 'public'
;
select view_name,column_name,table_name from
view_column_usage where view_name = 'event_remind_v';
select view_name,column_name,table_name from
information_schema.view_column_usage where view_name = 'event_remind_v';
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kyle@actarg.com, tgl@sss.pgh.pa.us
Subject: Re: information_schema problem
In-Reply-To: <4362AEFB.4040603@actarg.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox