From: Todd Kennedy <todd.kennedy@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Simple plpgsql question
Date: Thu, 13 Apr 2006 23:38:18 -0400
Message-ID: <226d83de0604132038k66d58e75vc6d290efcba9f2f3@mail.gmail.com> (raw)
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 = 'DELETE' THEN
UPDATE system_info SET value=(value::integer)-1 WHERE name =
'total_users'
RETURN OLD;
ELSEIF TG_OP = 'INSERT' THEN
UPDATE system_info SET value=(value::integer)+1 WHERE name =
'total_users';
INSERT 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!
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: todd.kennedy@gmail.com
Subject: Re: Simple plpgsql question
In-Reply-To: <226d83de0604132038k66d58e75vc6d290efcba9f2f3@mail.gmail.com>
* 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