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



view thread (4+ messages)  latest in thread

Message-ID: <20050317034816.GA13674@winnie.fuhr.org>
Permalink:  ../20050317034816.GA13674@winnie.fuhr.org/
Also on:    postgresql.org/message-id/20050317034816.GA13674@winnie.fuhr.org

 · 

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: 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