Simple plpgsql question
От | Todd Kennedy |
---|---|
Тема | Simple plpgsql question |
Дата | |
Msg-id | 226d83de0604132038k66d58e75vc6d290efcba9f2f3@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Simple plpgsql question
Re: Simple plpgsql question |
Список | pgsql-sql |
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 WHEREname = '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!
В списке pgsql-sql по дате отправления: