rules and functions as arguments

From: Jeff Davis <list-pgsql-general(at)empires(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: rules and functions as arguments
Date: 2002-08-29 06:40:48
Message-ID: 200208282340.48175.list-pgsql-general@empires.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message dima 2002-08-29 07:30:16 Re: Postgresql tuning..
Previous Message Tom Lane 2002-08-29 05:37:29 Re: oid pseudoattribute in rules