Message-ID: <4362AEFB.4040603@actarg.com>
Date: Fri, 28 Oct 2005 17:06:35 -0600
From: Kyle Bateman <kyle@actarg.com>
User-Agent: Mozilla Thunderbird 0.9 (X11/20041127)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us
Subject: information_schema problem
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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';