pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (4+ messages)  latest in thread

Message-ID: <42388A58.6020604@nypost.com>
Permalink:  ../42388A58.6020604@nypost.com/
Also on:    postgresql.org/message-id/42388A58.6020604@nypost.com

 · 

reply

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