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: 6.0.1.1.2.20050713132925.02c68258@marinconsulting.com (view raw or flat)
Thread:
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:

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

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 
help.

-Diana





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




Responses

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-2014 The PostgreSQL Global Development Group