pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple plpgsql question
3+ messages / 3 participants
[nested] [flat]
* Simple plpgsql question
@ 2006-04-14 03:38 Todd Kennedy <todd.kennedy@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Todd Kennedy @ 2006-04-14 03:38 UTC (permalink / raw)
To: 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 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!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Simple plpgsql question
@ 2006-04-14 09:03 Terry Lee Tucker <terry@esc1.com>
parent: Todd Kennedy <todd.kennedy@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Terry Lee Tucker @ 2006-04-14 09:03 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Simple plpgsql question
@ 2006-04-14 09:13 Volkan YAZICI <yazicivo@ttnet.net.tr>
parent: Todd Kennedy <todd.kennedy@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Volkan YAZICI @ 2006-04-14 09:13 UTC (permalink / raw)
To: Todd Kennedy <todd.kennedy@gmail.com>; +Cc: pgsql-sql
On Apr 13 11:38, Todd Kennedy wrote:
> 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.
If I understand you right, you're refering to a SERIAL column with id.
If so, you can use currval() function over related SEQUENCE. Because of
INSERT/DELETE and trigger will be executed in the same session, they'll
be able to see current value of related sequence. Below is an example
about this:
BEGIN;
CREATE SEQUENCE trig_t_seq START 1;
CREATE TABLE trig_t (
id bigint NOT NULL DEFAULT nextval('trig_t_seq'),
inf int
);
CREATE FUNCTION trig_t_row_count() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
RAISE NOTICE 'Current SEQUENCE value: %', currval('trig_t_seq');
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER trig_t_row_count_trig
AFTER INSERT
ON trig_t
FOR EACH ROW
EXECUTE PROCEDURE trig_t_row_count();
INSERT INTO trig_t (inf) VALUES (10);
INSERT INTO trig_t (inf) VALUES (20);
INSERT INTO trig_t (inf) VALUES (30);
ROLLBACK;
Regards.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-04-14 09:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-14 03:38 Simple plpgsql question Todd Kennedy <todd.kennedy@gmail.com>
2006-04-14 09:03 ` Terry Lee Tucker <terry@esc1.com>
2006-04-14 09:13 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
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