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

Re: plpgsql trigger problem

From: Jaime Casanova <systemguards(at)gmail(dot)com>
To: Diana Nemirovsky <diana(at)marinconsulting(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: plpgsql trigger problem
Date: 2005-07-15 22:19:01
Message-ID: c2d9e70e050715151953ba8fe6@mail.gmail.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.
> 

Give us the real code of the function, plz

-- 
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

In response to

pgsql-novice by date

Next:From: Lola LeeDate: 2005-07-16 11:52:41
Subject: Preserving Timestamp Information
Previous:From: Shawn GarbettDate: 2005-07-15 21:46:13
Subject: User Context Variables

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