pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp with plpgsql function called by trigger
4+ messages / 3 participants
[nested] [flat]
* help with plpgsql function called by trigger
@ 2005-03-16 19:34 Heather Johnson <hjohnson@nypost.com>
0 siblings, 2 replies; 4+ messages in thread
From: Heather Johnson @ 2005-03-16 19:34 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: help with plpgsql function called by trigger
@ 2005-03-17 03:48 Michael Fuhr <mike@fuhr.org>
parent: Heather Johnson <hjohnson@nypost.com>
1 sibling, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2005-03-17 03:48 UTC (permalink / raw)
To: Heather Johnson <hjohnson@nypost.com>; +Cc: pgsql-general@postgresql.org
On Wed, Mar 16, 2005 at 02:34:48PM -0500, Heather Johnson wrote:
> CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS '
What version of PostgreSQL are you using? The "opaque" type has
been deprecated since 7.3; recent versions should use "trigger".
And trigger functions aren't declared with arguments -- if the
function needs arguments, then use TG_ARGV. But I think your code
can use NEW instead of a function argument.
> 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';
Based on the rest of your description, I think you're looking for
something like this:
CREATE FUNCTION insert_bd_join_bd() RETURNS trigger AS '
DECLARE
bdid integer;
BEGIN
INSERT INTO behavioral_demographics (users_id) VALUES (NEW.uid);
bdid := currval(''behavioral_demographics_bdid_seq'');
INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, NEW.uid);
RETURN NULL;
END;
' LANGUAGE plpgsql VOLATILE;
See the "Trigger Procedures" section of the PL/pgSQL chapter in the
documentation to learn more about NEW (and OLD, TG_ARGV, etc.), and
see the "Sequence Manipulation Functions" section of the "Functions
and Operators" chapter to learn more about currval().
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: help with plpgsql function called by trigger
@ 2005-03-17 09:51 Alban Hertroys <alban@magproductions.nl>
parent: Heather Johnson <hjohnson@nypost.com>
1 sibling, 1 reply; 4+ messages in thread
From: Alban Hertroys @ 2005-03-17 09:51 UTC (permalink / raw)
To: Heather Johnson <hjohnson@nypost.com>; +Cc: pgsql-general@postgresql.org
Heather Johnson wrote:
> 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';
Wouldn't it be nicer to put the second part of that SP in a trigger on
behavioral_demographics? That can only work if inserting into users is
the only way to insert records into behavioral_demographics, of course.
I don't know the names of your columns in behavioral_demographics, but
it would look something like this:
CREATE FUNCTION insert_bd () RETURNS trigger AS '
begin
INSERT INTO behavioral_demographics (users_id)
VALUES (NEW.users_id); -- fires trigger on bd
end;
' LANGUAGE 'plpgsql';
CREATE FUNCTION insert_join_bd () RETURNS trigger AS '
begin
INSERT INTO join_bd (bd_id, users_id)
VALUES (NEW.bd_id, NEW.users_id);
end;
' LANGUAGE 'plpgsql';
CREATE TRIGGER insert_bd_on_users
AFTER INSERT ON users
FOR EACH ROW EXECUTE PROCEDURE insert_bd();
CREATE TRIGGER insert_join_bd_on_users
AFTER INSERT ON behavioral_demographics
FOR EACH ROW EXECUTE PROCEDURE insert_join_bd();
Considering these SP's are basically simple SQL statements, the triggers
could also be implemented as a set of query rewrite rules (see CREATE
RULE). That's usually more efficient, but I don't have a lot of
experience with those...
Regards,
--
Alban Hertroys
MAG Productions
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: help with plpgsql function called by trigger
@ 2005-03-17 16:06 Heather Johnson <hjohnson@nypost.com>
parent: Alban Hertroys <alban@magproductions.nl>
0 siblings, 0 replies; 4+ messages in thread
From: Heather Johnson @ 2005-03-17 16:06 UTC (permalink / raw)
To: Alban Hertroys <alban@magproductions.nl>; +Cc: pgsql-general@postgresql.org
Thank you! That *is* nicer. And thanks to Michael Fuhr too for his
reference to the appropriate docs and suggestions. Between your two
responses, I have a much better sense of how to go about this and where
to look for extra help.
Heather
Alban Hertroys wrote:
> Heather Johnson wrote:
>
>> 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';
>
>
> Wouldn't it be nicer to put the second part of that SP in a trigger on
> behavioral_demographics? That can only work if inserting into users is
> the only way to insert records into behavioral_demographics, of course.
>
> I don't know the names of your columns in behavioral_demographics, but
> it would look something like this:
>
> CREATE FUNCTION insert_bd () RETURNS trigger AS '
> begin
> INSERT INTO behavioral_demographics (users_id)
> VALUES (NEW.users_id); -- fires trigger on bd
> end;
> ' LANGUAGE 'plpgsql';
>
> CREATE FUNCTION insert_join_bd () RETURNS trigger AS '
> begin
> INSERT INTO join_bd (bd_id, users_id)
> VALUES (NEW.bd_id, NEW.users_id);
> end;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER insert_bd_on_users
> AFTER INSERT ON users
> FOR EACH ROW EXECUTE PROCEDURE insert_bd();
>
> CREATE TRIGGER insert_join_bd_on_users
> AFTER INSERT ON behavioral_demographics
> FOR EACH ROW EXECUTE PROCEDURE insert_join_bd();
>
>
> Considering these SP's are basically simple SQL statements, the triggers
> could also be implemented as a set of query rewrite rules (see CREATE
> RULE). That's usually more efficient, but I don't have a lot of
> experience with those...
>
> Regards,
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-03-17 16:06 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-16 19:34 help with plpgsql function called by trigger Heather Johnson <hjohnson@nypost.com>
2005-03-17 03:48 ` Michael Fuhr <mike@fuhr.org>
2005-03-17 09:51 ` Alban Hertroys <alban@magproductions.nl>
2005-03-17 16:06 ` Heather Johnson <hjohnson@nypost.com>
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