pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
help 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>
  2005-03-17 03:48 ` Re: help with plpgsql function called by trigger Michael Fuhr <mike@fuhr.org>
  2005-03-17 09:51 ` Re: help with plpgsql function called by trigger Alban Hertroys <alban@magproductions.nl>
  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-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>
  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-16 19:34 help with plpgsql function called by trigger Heather Johnson <hjohnson@nypost.com>
@ 2005-03-17 09:51 ` Alban Hertroys <alban@magproductions.nl>
  2005-03-17 16:06   ` Re: help with plpgsql function called by trigger 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-16 19:34 help with plpgsql function called by trigger Heather Johnson <hjohnson@nypost.com>
  2005-03-17 09:51 ` Re: help with plpgsql function called by trigger Alban Hertroys <alban@magproductions.nl>
@ 2005-03-17 16:06   ` Heather Johnson <hjohnson@nypost.com>
  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