Re: Reference to multiple cols - Mailing list pgsql-novice

After looking through the inheritance chapter in the PostgreSQL docs
(http://www.zill.net/pgdocs/inherit.html) I found the solution:

lex=# SELECT name, relname FROM animal, pg_class WHERE animal.tableoid =
pg_class.oid;
   name   | relname
----------+---------
 ralf     | dog
 penelope | cat
(2 rows)


good luck,
 - lex

On Sun, 2003-01-12 at 19:00, Ville Jungman wrote:
> This seems to be exactly what i needed at first glance.
>
> ---
>
> >- Child columns are not available to the parent (does not inherit up).
>
> But I can read a child columns using oid (ok, i'll maybe add that serial id
> column) value that i've read from the parent table ...but still don't know
> which table. (I managed to get the needed values from child tables but by
> inefficient ways.)
>
> ---
>
> Bruno Wolff III wrote:
> >>If you use the suggestion to also have an animal type column in each
> >>table,
> >>then you can check the animal type to see which auxillary table to
> >>look at.
>
> Now i need only one column in animal table to do this. This is quite good
> way. I'll maybe use it by now, until the way to determine the child table
> name (or something other successful way) is found.
>
> ---
>
> Inheritances might be useful to many other purposes, too. Thank You*2
> much*x!
>
> ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland
> tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak
> usko Herraan Jeesukseen, niin sinä pelastut. (apt. 16:31)
>
>
>
>
> >From: Lex Berezhny <LBerezhny@DevIS.com>
> >To: Ville Jungman <ville_jungman@hotmail.com>
> >CC: pgsql-novice@postgresql.org,"Martin N. Hudson"
> ><MHudson@DevIS.com>,Cristina Mossi Rhein <CMossiRhein@DevIS.com>
> >Subject: Re: [NOVICE] Reference to multiple cols
> >Date: 12 Jan 2003 12:14:51 -0500
> >
> >Relational databases are not particularly suited for building
> >heterogeneous hierarchies. You may try looking at some of the object
> >oriented databases which are perfect for this kind of relationship.
> >
> >Having said that, you might also be satisfied with simply using the
> >PostgreSQL Inheritance feature. Personally, I have not yet used this on
> >production software but I have played with it enough to know that it
> >works *g*
> >
> >The inheritance concept follows that you create a parent table (animal)
> >and then all of its children will inherit from this table. Thus, when
> >you do a select on the animal table, it will return all of its children,
> >even if they are heterogeneous.
> >
> >Relevant DDL:
> >
> >       1 REATE TABLE animal (
> >       2     name text NOT NULL
> >       3 );
> >       4
> >       5 CREATE TABLE dog (
> >       6     barking_volume integer,
> >       7     slobber integer
> >       8 )
> >       9 INHERITS (animal);
> >      10
> >      11 CREATE TABLE cat (
> >      12     lazyness integer,
> >      13     shedding_amount integer
> >      14 )
> >      15 INHERITS (animal);
> >
> >There are two things to nice, first is that I no longer need a serial id
> >(if your tables interacts with other parts of the system you might want
> >the ID back in there though ;-), the second thing to notice is the
> >INHERITS declaration (lines 9 & 15) which link the two tables to the
> >animal table via inheritance and cause them to acquire the name column.
> >
> >Relevant queries:
> >
> >lex=# INSERT INTO dog VALUES ('ralf', 2, 5);
> >INSERT 17113 1
> >lex=# INSERT INTO cat VALUES ('penelope', 9, 2);
> >INSERT 17114 1
> >lex=# SELECT * FROM animal;
> >    name
> >----------
> >  ralf
> >  penelope
> >(2 rows)
> >lex=# SELECT * FROM dog;
> >  name | barking_volume | slobber
> >------+----------------+---------
> >  ralf |              2 |       5
> >(1 row)
> >lex=# SELECT * FROM cat;
> >    name   | lazyness | shedding_amount
> >----------+----------+-----------------
> >  penelope |        9 |               2
> >(1 row)
> >lex=# DELETE FROM animal;
> >DELETE 2
> >
> >Subtle points:
> >
> >   - Inserting into dog and cat makes the record available in animal.
> >   - Deleting from animal also deletes the record from child tables.
> >   - Child columns are not available to the parent (does not inherit up).
> >   - Parent columns are part of the child (inherits down).
> >
> >Unfortunately, there seems to be no way to find the child table name of
> >a row retrieved from the parent table. But I played with this several
> >months ago and don't remember all the gory details, I will see if I can
> >dig up some of my queries.
> >
> >If you like the approach I described above then I would encourage you to
> >first learn about it as much as possible before implementing it. The
> >reason is that it's not standard amongst RDBMSs and so there is little
> >experience with this kind of setup.
> >
> >happy hacking,
> >
> >  - lex
> >
> >On Sun, 2003-01-12 at 08:06, Ville Jungman wrote:
> > > Thank You for help!
> > >
> > > Still i have one problem left. How can i determine which table's row
> > > references to the main table row if i don't want to read every
> >referencing
> > > table and search the right row. That was a part of the original problem
> >and
> > > idea why i first wanted that the main table had the column that
> >references
> > > to other tables.
> > >
> > > ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland
> > > tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak
> > > usko Herraan Jeesukseen, niin sinä pelastut. (apt. 16:31)
> > >
> > >
> > > >From: Bruno Wolff III <bruno@wolff.to>
> > > >To: Josh Berkus <josh@agliodbs.com>
> > > >CC: Ville Jungman
> ><ville_jungman@hotmail.com>,pgsql-novice@postgresql.org
> > > >Subject: Re: [NOVICE] Reference to multiple cols
> > > >Date: Sat, 11 Jan 2003 18:51:21 -0600
> > > >
> > > >On Sat, Jan 11, 2003 at 14:00:14 -0800,
> > > >   Josh Berkus <josh@agliodbs.com> wrote:
> > > > >
> > > > > This should give you a system in which animal_id is the primary key
> >for
> > > > > each table, and therefore there is a one-for-one relationship
> >between
> > > > > the animal table and each of the dog and cat tables, and would
> >prevent
> > > > > you from deleting a referenced record from the animal table.
> > > > >
> > > > > You would need an additional trigger to prevent duplication
> >*between*
> > > > > the dog and cat tables.
> > > >
> > > >There is a way to do this without triggers. You add an animal type
> > > >field to all of the tables. The foreign keys then should use both
> >animal id
> > > >and animal type (which means you need to make animal_id and animal_type
> > > >a combined unique key on the animal table). You add a constraint to the
> > > >animal
> > > >specific tables forcing the animal type to be the appropiate type.
> > > >
> > > >---------------------------(end of
> >broadcast)---------------------------
> > > >TIP 3: if posting/reading through Usenet, please send an appropriate
> > > >subscribe-nomail command to majordomo@postgresql.org so that your
> > > >message can get through to the mailing list cleanly
> > >
> > >
> > > _________________________________________________________________
> > > Protect your PC - get McAfee.com VirusScan Online
> > > http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> >--
> >Lex Berezhny <LBerezhny@DevIS.com>
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
> _________________________________________________________________
> The new MSN 8 is here: Try it free* for 2 months
> http://join.msn.com/?page=dept/dialup
--
Lex Berezhny <LBerezhny@DevIS.com>