Re: My brain hurts - update field based on value of another table's field - Mailing list pgsql-general
From | Pat M |
---|---|
Subject | Re: My brain hurts - update field based on value of another table's field |
Date | |
Msg-id | S5or7.11646$L8.1429190@news2.rdc1.bc.home.com Whole thread Raw |
In response to | Re: My brain hurts - update field based on value of another table's field (missive@frontiernet.net (Lee Harr)) |
List | pgsql-general |
Ok, here's what they are * area - arbitrary name for a collection of sites (optional) * site name for a collection of buildings (may only be one building) optional * building - a single building not optional name for a collection of rooms * zone - sub area of a building not optional name for a collection of rooms. * rooms (not listed) The main record here is Building. Areas and sites are optional ways of grouping buildings. Zones and rooms are required. Since the area and site are optional and arbitrary (you may change your organizational chart) I need a way of updating the children of that record to reflect the changes. I can do it easy enough in php, just don't know how with postgres. Here's an example of the php script. Probably won't indent properly... $building_id is the current building $sel_BuildingSite is the new site for this building if ($but_UpdateBuilding) { $txt_buildingname=trim($txt_buildingname); if ($txt_buildingname) { $check=pg_Exec($connection,"select building_id from buildings where building_id='$building_id';"); if ($check) { if(pg_NumRows($check)==1) { $check=pg_Exec($connection," update zones set zone_site='$sel_BuildingSite' where zone_building='$building_id';"); $check=pg_Exec($connection," update rooms set room_site='$sel_BuildingSite' where room_building='$building_id';"); $check=pg_Exec($connection," update buildings set building_name='$txt_buildingname', building_site='$sel_BuildingSite' where building_id='$building_id';"); } else { $error="Building ID Not found"; } } else { $error="Error - check is null for this building_id!"; } } } What I'd like to do is have the php script change the building_site field value then have postgres update the zone_site and room_site fields by itself. The more I read the docs (thus the brain pain) the more I'm convinced I need a trigger and function. I just can't seem to figure out how they work. Yes, I will be moving to transactions and more error checking, just trying to figure out how I'm going to do this before I get too involved 8) "Lee Harr" <missive@frontiernet.net> wrote in message news:9okp4i$2q58$1@news.tht.net... > On Sun, 23 Sep 2001 03:56:50 GMT, Pat M <pmeloy@removethispart.home.com> wrote: > > I'm just a hobbyist so this is probably atroceous, but I'm trying to do > > something like the following. Sorry its not in real SQL format. I know how > > to auto-update by referencing to a primary key of another table, but what if > > you want a field to change along with a non-unique field from a different > > table? Here's an example of what I mean > > > > table areas > > ------------- > > area_id serial primary key > > area_name text > > > > table sites > > ---------- > > site_id serial primary key > > site_name text > > site_area int references areas on delete cascade > > > > table buildings > > ------------ > > building_id serial primary key > > building_name text > > building_area int <--- needs to change when site_area changes > > building_site int references sites on delete cascade > > > > I think you would do this by _not_ having building_area at all. > You have a building_site, which has a site_area. Is it possible > to have a building_area that is _different_ from the site_area > of the building_site? That does not make sense to me. > > > > table zones > > ------------ > > zone_id serial primary key > > zone_name text > > zone_area int <--- needs to change when building_area changes > > zone_site int <--- needs to change when building_site changes > > zone_building int references buildings on delete cascade > > > > It is really difficult to help you not knowing what these "site" and > "area" and "zone" things are. > > > > None of the primary keys will be changing of course. But the area a building > > is in may change (area being an arbitrary designation, not municiple > > boundaries), as may other fields as I work my way through building the data. > > > > I know I can join things together in queries, avoiding all this, but it gets > > real confusing trying to join 12 tables, and slow... I want to be able to > > get the area from the buildings table and not have to join three tables just > > to find out what area it belongs to. Unless someone knows an easier way than > > select area_name from areas,sites,buildings where area_id=site_area and > > site_id=building_id and building_id=1; Speed and easy queries are my focus, > > not disk space or ram savings. > > > > I looked at foreign keys, but they get uptight when the referenced field > > isn't unique. On update cascade would have been wonderful 8( > > > > I looked at inheritance, but I don't think its what I had in mind. > > > > I'm thinking I have to delve into the horrors that are triggers and > > functions... > > > > Any cool ideas floating around out there? > > > >
pgsql-general by date: