Thread

1. create view ... select fld,'constant',fld ...

   Will Trillich <will@serensoft.com> — 2003-01-31T23:51:50Z

   is this a stupid idea?
   
   	create table _kid (
   		id serial,
   		datum varchar(80),
   		other bigint,
   		-- many-to-one, kid-to-papa relation
   		parent int4 references papa(id)
   	);
   
   	create view kid as
   	select
   		id,
   		datum,
   		other,
   		parent,
   		-- which table is our parent? right here:
   		'papa' as parent_table
   	from
   		_kid;
   
   then we can do things like
   
   	->execute("select parent_table from $table")
   
   but is it a performance-sucker? or bandwidth-killer? or just
   all-round horrid idea?
   
   or not?
   
   -- 
   There are 10 kinds of people:
   ones that get binary, and ones that don't.
    
   will@serensoft.com
   http://sourceforge.net/projects/newbiedoc -- we need your brain!
   http://www.dontUthink.com/ -- your brain needs us!
    
   Looking for a firewall? Do you think smoothwall sucks? You're
   probably right... Try the folks at http://clarkconnect.org/ !
   
   

2. Re: create view ... select fld,'constant',fld ...

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-02-01T04:21:05Z

   will trillich <will@serensoft.com> writes:
   > is this a stupid idea?
   > [create view such that]
   > then we can do things like
   > 	->execute("select parent_table from $table")
   
   Uh, do you really want to impose overhead on *every* query to make
   that one operation faster?
   
   I'd go with a query on the system catalogs, for example
   
   regression=# create table p (f1 int);
   CREATE TABLE
   regression=# create table c (f2 int) inherits(p);
   CREATE TABLE
   regression=# select relname from pg_class where
   regression-# oid = (select inhparent from pg_inherits where
   regression(# inhrelid = (select oid from pg_class where relname = 'c'));
    relname
   ---------
    p
   (1 row)
   
   Note this example will provoke a failure if 'c' inherits from multiple
   parents --- you could add a condition on pg_inherits.inhseqno to deal
   with that.
   
   Also, as of 7.3 you'd need to think about schema issues leading
   to ambiguities of relnames.  In 7.3 I'd be inclined to write
   
   regression=# select inhparent::regclass from pg_inherits where inhrelid = 'c'::regclass;
    inhparent
   -----------
    p
   (1 row)
   
   since the regclass I/O converters do the right thing with schemas.
   
   			regards, tom lane
   
   

3. Re: create view ... select fld,'constant',fld ...

   Will Trillich <will@serensoft.com> — 2003-02-01T04:38:09Z

   On Fri, Jan 31, 2003 at 11:21:05PM -0500, Tom Lane wrote:
   > will trillich <will@serensoft.com> writes:
   > > is this a stupid idea?
   > > [create view such that]
   > > then we can do things like
   > > 	->execute("select parent_table from $table")
   > 
   > Uh, do you really want to impose overhead on *every* query to make
   > that one operation faster?
   
   that's what i was asking. is that a lot of overhead?
   
   > I'd go with a query on the system catalogs, for example
   > 
   > regression=# create table p (f1 int);
   > CREATE TABLE
   > regression=# create table c (f2 int) inherits(p);
                                          ????????
   > CREATE TABLE
   > regression=# select relname from pg_class where
   > regression-# oid = (select inhparent from pg_inherits where
   > regression(# inhrelid = (select oid from pg_class where relname = 'c'));
   
   my bad, i probably used the wrong terminology.
   
   this isn't inheritance -- it's many-to-one relationship between
   tables. below, org can have many projects; projects are tied to
   people via many team records.
   
   	person
   		id
   		fname
   		lname
   	
   	org
   		name
   		addr
   	
   	project
   		name
   		objective
   		due_date
   		org.id => many projects to one org
   	
   	team
   		person.id => many teams (projects) for one person
   		project.id => many teams (people) for one project
   		joined_date
   
   from what i can tell, the trigger function calls are all there
   is to indicate the use of foreign keys. and there's some serious
   hair in parsing those \0 strings to get what you want out of
   them. seems like there has to be some other mechanism that'd
   work with less obtusification.
   
   -- 
   There are 10 kinds of people:
   ones that get binary, and ones that don't.
    
   will@serensoft.com
   http://sourceforge.net/projects/newbiedoc -- we need your brain!
   http://www.dontUthink.com/ -- your brain needs us!
    
   Looking for a firewall? Do you think smoothwall sucks? You're
   probably right... Try the folks at http://clarkconnect.org/ !