rules and functions as arguments

Поиск
Список
Период
Сортировка
От Jeff Davis
Тема rules and functions as arguments
Дата
Msg-id 200208282340.48175.list-pgsql-general@empires.org
обсуждение исходный текст
Ответы Re: rules and functions as arguments  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: oid pseudoattribute in rules
Следующее
От: jerome
Дата:
Сообщение: Postgresql tuning..