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

This is it! Thank You very much, Lex. Case closed :-)

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
>Subject: Re: [NOVICE] Reference to multiple cols
>Date: 12 Jan 2003 19:52:29 -0500
>
>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>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster


_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail