Re: help with plpgsql function called by trigger

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/