Thread: create view ... select fld,'constant',fld ...

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/ !

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

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/ !