rules and functions as arguments - Mailing list pgsql-general

From Jeff Davis
Subject rules and functions as arguments
Date
Msg-id 200208282340.48175.list-pgsql-general@empires.org
Whole thread Raw
Responses Re: rules and functions as arguments  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
I created a rule that looks like the following:

create rule a_rule as on insert to a do
(
insert into heir_r (id,super_id) values(NEW.id,NEW.parid);
insert into heir_r select NEW.id,super_id from heir_r where id=NEW.parid
);

Then, if I do:
INSERT INTO a (id, parid, name) values(NEXVAL('a_seq'),0,'text');

Then each subsequent action (generated from the rule) replaces "NEW.id" with
the result of a new call to NEXTVAL('a_seq'). That means that the NEW.id in
the first statement in the above rule is different from the actual value
passed to the insert statement that activated the rule.

Is there a way I can make it only do a function call once, and use the value
after that for all NEW.id's?

The solution that I have come up with is to do:
SELECT NEXTVAL('a_seq');
INSERT INTO a (id, parid, name) values(CURRVAL('a_seq'),0,'text');

So that new numbers aren't generated. However, the whole thing seems
counterintuitive to me, since in most programming languages if you pass a
function call as an argument, it just passes the return value of the
function, it doesn't do another call when inside the body of the function.

Does anyone have any thoughts? A better way to solve my problem? I don't want
people to have to know that I have a rule in place (i.e. I don't want to have
to remember not to use NEXTVAL()). However, I also want those additional
inserts to fire.

Is a trigger a better option here perhaps?

Thanks,
    Jeff

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: oid pseudoattribute in rules
Next
From: jerome
Date:
Subject: Postgresql tuning..