From: Heather Johnson <hjohnson@nypost.com>
To: pgsql-general@postgresql.org
Subject: help with plpgsql function called by trigger
Date: Wed, 16 Mar 2005 14:34:48 -0500
Message-ID: <42388A58.6020604@nypost.com> (raw)
In-Reply-To: <421B9E75.9050304@nypost.com>
References: <421B8D2D.6050800@nypost.com>
<20050222200915.GA71591@winnie.fuhr.org>
<421B9674.4060807@nypost.com>
<1109105459.30529.119.camel@state.g2switchworks.com>
<421B9E75.9050304@nypost.com>
Hello--
I need to make sure that every time a row is inserted into a table
called "users" rows are automatically inserted into two other tables:
"join_bd" and "behavior_demographics". The inserts on join_bd and
behavior_demographics need to create rows that are keyed to the users
table with an integer id (called "users_id"). The join_bd row that's
created also needs to contain a key for a record in
behavior_demographics (bd_id). Here's what I did to try and accomplish this:
CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS '
declare
r RECORD;
uid ALIAS FOR $1;
begin
INSERT INTO behavioral_demographics (users_id) VALUES (uid);
SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid;
INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid);
end;
' LANGUAGE 'plpgsql';
Now I want to trigger this function whenever there is an insert on the
users table, so I did this:
CREATE TRIGGER insert_bd_join_bd_on_users AFTER INSERT ON users
FOR EACH ROW EXECUTE PROCEDURE insert_bd_join_bd();
The problem is that I need to be able to send the value of the users_id
that was generated by the insert into users to insert_bd_join_bd(). The
users id value is generated by a sequence. So I think I'd need something
like this:
CREATE TRIGGER insert_bd_join_bd_on_users AFTER INSERT ON users
FOR EACH ROW EXECUTE PROCEDURE insert_bd_join_bd(***USERS ID GENERATED
BY SEQ FROM LAST USERS INSERT***);
I'm new to writing plpgsql and to triggers, so I'm probably missing
something, or doing this wrong, but I can't figure out from the docs how
to send this value to the function. Can someone help point me to some
docs that might help, or tell me why I'm on the wrong track?
Thanks so much!
Heather Johnson
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: hjohnson@nypost.com, pgsql-general@postgresql.org
Subject: Re: help with plpgsql function called by trigger
In-Reply-To: <42388A58.6020604@nypost.com>
References: <421B8D2D.6050800@nypost.com> <20050222200915.GA71591@winnie.fuhr.org> <421B9674.4060807@nypost.com> <1109105459.30529.119.camel@state.g2switchworks.com> <421B9E75.9050304@nypost.com> <42388A58.6020604@nypost.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox