pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing new.id in the WHERE condition of one UPDATE inside ON INSERT rule
3+ messages / 2 participants
[nested] [flat]
* Using new.id in the WHERE condition of one UPDATE inside ON INSERT rule
@ 2010-02-25 15:19 Aron <auryn@wanadoo.es>
0 siblings, 1 reply; 3+ messages in thread
From: Aron @ 2010-02-25 15:19 UTC (permalink / raw)
To: pgsql-sql
I would like to avoid using "SELECT MAX(id) FROM my_table" (it doesn't seem a
good method), but if I use "new.id", I get new id values, not the "id"
inserted with the rule, and the condition is always false.
Complete example (it works because it doesn't use new.id inside condition):
DROP TABLE IF EXISTS my_table;
DROP TABLE IF EXISTS my_other_table;
CREATE TABLE my_other_table (
id serial PRIMARY KEY,
my_other_cost INTEGER
);
INSERT INTO my_other_table(my_other_cost) VALUES(155);
INSERT INTO my_other_table(my_other_cost) VALUES(277);
CREATE TABLE my_table (
id serial PRIMARY KEY,
id_other INTEGER,
my_cost INTEGER
);
CREATE OR REPLACE RULE my_insert AS
ON INSERT TO my_table
DO ALSO
UPDATE my_table SET my_cost = my_other_table.my_other_cost
FROM my_other_table
WHERE new.id_other = my_other_table.id
AND my_table.id = (SELECT MAX(id) FROM my_table); -- I want " = new.id"
here, but doesn't work as I expect
INSERT INTO my_table(id_other) VALUES(1);
INSERT INTO my_table(id_other) VALUES(2);
SELECT * FROM my_table;
Thanks
--
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Using new.id in the WHERE condition of one UPDATE inside ON INSERT rule
@ 2010-02-25 15:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Aron <auryn@wanadoo.es>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2010-02-25 15:28 UTC (permalink / raw)
To: Aron <auryn@wanadoo.es>; +Cc: pgsql-sql
Aron <auryn@wanadoo.es> writes:
> I would like to avoid using "SELECT MAX(id) FROM my_table" (it doesn't seem a
> good method), but if I use "new.id", I get new id values, not the "id"
> inserted with the rule, and the condition is always false.
"new.id" is a macro, which in this example will be expanded into a
nextval() function call, which is why it doesn't work --- the nextval()
in the WHERE condition will produce a different value from the one in
the original INSERT. You would be far better off using a trigger here
instead of a rule.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Using new.id in the WHERE condition of one UPDATE inside ON INSERT rule
@ 2010-03-01 16:30 Aron <auryn@wanadoo.es>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Aron @ 2010-03-01 16:30 UTC (permalink / raw)
To: pgsql-sql
On Jueves 25 Febrero 2010 16:28:56 usted escribió:
> Aron <auryn@wanadoo.es> writes:
> > I would like to avoid using "SELECT MAX(id) FROM my_table" (it doesn't
> > seem a good method), but if I use "new.id", I get new id values, not the
> > "id" inserted with the rule, and the condition is always false.
>
> "new.id" is a macro, which in this example will be expanded into a
> nextval() function call, which is why it doesn't work --- the nextval()
> in the WHERE condition will produce a different value from the one in
> the original INSERT. You would be far better off using a trigger here
> instead of a rule.
>
> regards, tom lane
>
Thank you very much.
I've used this trigger succesfully:
CREATE OR REPLACE FUNCTION copy_cost RETURNS "trigger" AS '
DECLARE
change_other BOOL;
BEGIN
IF (tg_op = ''UPDATE'') THEN
IF (new.id_other <> old.id_other) THEN
change_other = true;
ELSE
change_other = false;
END IF;
END IF;
IF (tg_op = ''INSERT'' AND new.my_cost IS NULL)
OR change_other) THEN
new.my_cost = (
SELECT my_other_cost
FROM my_other_table
WHERE id = new.id_other
);
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER copy_cost__trigger
BEFORE INSERT OR UPDATE ON my_table
FOR EACH ROW
EXECUTE PROCEDURE copy_cost();
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-03-01 16:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-25 15:19 Using new.id in the WHERE condition of one UPDATE inside ON INSERT rule Aron <auryn@wanadoo.es>
2010-02-25 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-01 16:30 ` Aron <auryn@wanadoo.es>
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