Skip site navigation (1) Skip section navigation (2)

plpgsql trigger problem

From: Diana Nemirovsky <diana(at)marinconsulting(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: plpgsql trigger problem
Date: 2005-07-13 20:46:52
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
I'm trying to write a trigger (on insert or update) on one table that will 
update values in another table, and I'm having trouble with it. There are 3 
tables in question, person, household, and person_household (linking people 
to households). There are foreign key constraints on the person_household 
table, if that matters, but no triggers on any table except person, which 
is the one I'm working on. I'm dealing with parents and kids, and a parent 
will only be in one household.

I can write SQL statements that will work outside of the trigger to get me 
the records I want, but they don't seem to work correctly within the 
trigger. I've put my pseudo code place holders in # signs.

The fist thing I'd need to do is

SELECT id_household
FROM person_household
WHERE id_person=NEW.id_person  --find the household that person belongs to

with that ID I get, then do the following

--get the other parent in the household (if any)
SELECT id_person, name_first
FROM person NATURAL INNER JOIN person_household
WHERE (person_household.id_household=#id_household (from above query)# AND 
person.grade IS NULL) --leave out the kids

then with that name I get, I need to get the household record and update it:

         UPDATE household
         SET salutation = NEW.first_name || #person.name_first selected in 
2nd query above)#
         WHERE id_household = #id_household#

This works in theory, but not in practice. I've tried SELECT INTO as well, 
also no luck. I could write this in my sleep in a different language, but 
I'm new to plpgsql, and this is kicking my, well, you know.  Thanks for any 


Diana Nemirovsky
Marin Consulting, Inc.
770 Menlo Ave, Ste 223
Menlo Park, CA 94025
v (650) 617-8699
f (650) 833-0790 


pgsql-novice by date

Next:From: Doug HallDate: 2005-07-13 21:26:01
Subject: Re: Help! Not enough swap space?
Previous:From: Doug HallDate: 2005-07-13 20:35:35
Subject: Help! Not enough swap space?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group