DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=onIrBR1WUHZR+gLi61uXc29lazO5KfMxxCJekyhJ1VDr0RHJjUT76us4h46Oqcdn+bxQBEdnCVwzupCms5zhGhxuYGzS5G4fvKFsIp6/rfgXAPFCJwpWazVce3ymFX0ZShd2kkx19kKy96b3rmTvJfv3/NTRF/KJ1yyEh+htj3Q=
Message-ID: <226d83de0604132038k66d58e75vc6d290efcba9f2f3@mail.gmail.com>
Date: Thu, 13 Apr 2006 23:38:18 -0400
From: "Todd Kennedy" <todd.kennedy@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Simple plpgsql question
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,

I have, what I hope to be, a simple question about plpgsql.

I have a trigger on a table right now that updates a count everytime
that a new record is entered into a database (or removed).

What I'd also like to do is have it create a new row in a different
table using the automatically assigned id as a reference, but I'm
unsure of how to obtain the id of the newly created row in the first
table.

Example:
CREATE TABLE system_info (
id serial PRIMARY KEY,
name varchar(255),
value varchar(255)
);
INSERT INTO system_info (name,value) VALUES ('total_users','0');

CREATE TABLE master (
id serial PRIMARY KEY,
name varchar(32) NOT NULL CHECK ( name <> ''),
UNIQUE(name)
);

CREATE TABLE slave (
id serial PRIMARY KEY,
master_id integer REFERENCES master (id),
additional_info text
);

CREATE OR REPLACE FUNCTION update_users() RETURNS trigger AS $$
BEGIN
    IF TG_OP =3D 'DELETE' THEN
        UPDATE system_info SET value=3D(value::integer)-1 WHERE name =3D
'total_users'
        RETURN OLD;
    ELSEIF TG_OP =3D 'INSERT' THEN
        UPDATE system_info SET value=3D(value::integer)+1 WHERE name =3D
'total_users';
=09INSERT INTO slave (master_id) VALUES (THIS IS WHAT I NEED TO KNOW);
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users AFTER INSERT OR DELETE ON master
    FOR EACH ROW EXECUTE PROCEDURE update_users();


The part I need to know is the INSERT INTO statement in the procedure.

Any help would be great.

Thanks!