help with plpgsql function called by trigger - Mailing list pgsql-general

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