From: Michael Fuhr <mike@fuhr.org>
To: Heather Johnson <hjohnson@nypost.com>
Cc: pgsql-general@postgresql.org
Subject: Re: help with plpgsql function called by trigger
Date: Wed, 16 Mar 2005 20:48:16 -0700
Message-ID: <20050317034816.GA13674@winnie.fuhr.org> (raw)
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>
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/
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: mike@fuhr.org, hjohnson@nypost.com, pgsql-general@postgresql.org
Subject: Re: help with plpgsql function called by trigger
In-Reply-To: <20050317034816.GA13674@winnie.fuhr.org>
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> <20050317034816.GA13674@winnie.fuhr.org>
* 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