From: Terry Lee Tucker <terry@esc1.com>
To: pgsql-sql@postgresql.org
Subject: Re: Simple plpgsql question
Date: Fri, 14 Apr 2006 05:03:10 -0400
Message-ID: <200604140503.10335.terry@esc1.com> (raw)
In-Reply-To: <226d83de0604132038k66d58e75vc6d290efcba9f2f3@mail.gmail.com>
References: <226d83de0604132038k66d58e75vc6d290efcba9f2f3@mail.gmail.com>
On Thursday 13 April 2006 11:38 pm, "Todd Kennedy" <todd.kennedy@gmail.com>
thus communicated:
--> 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!
-->
--> ---------------------------(end of broadcast)---------------------------
--> TIP 4: Have you searched our list archives?
-->
--> http://archives.postgresql.org
-->
INSERT INTO slave (master_id) VALUES (new.id);
The buffer NEW contains all the "new" data.
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: terry@esc1.com
Subject: Re: Simple plpgsql question
In-Reply-To: <200604140503.10335.terry@esc1.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