Обсуждение: Order of Rules
I read at the docs that rules are executed before the query that generated them. Is there any way to change this? Here's my problem: I have two tables, A and B, and after an INSERT on table A, I want to update an specific field on table B, with the result of a sum in A. To do this I needed some information about the row just inserted, so I used rules. As my sum is running before the insertion, its result is wrong. Any ideas? Tks -- Nelio Alves Pereira Filho IFX Networks - www.ifx.com.br +55 11 3365-5863 nelio@ifx.com.br
Sounds like you need to use a trigger set to fire after the insert on table A Alex. On Tue, 30 Jan 2001, Nelio Alves Pereira Filho wrote: > I read at the docs that rules are executed before the query that > generated them. Is there any way to change this? > > Here's my problem: I have two tables, A and B, and after an INSERT on > table A, I want to update an specific field on table B, with the result > of a sum in A. To do this I needed some information about the row just > inserted, so I used rules. As my sum is running before the insertion, > its result is wrong. > > Any ideas? > > Tks > > -- > Nelio Alves Pereira Filho > IFX Networks - www.ifx.com.br > +55 11 3365-5863 > nelio@ifx.com.br >
Yes, but how can I pass a reference of the row just inserted to the trigger?? adb wrote: > > Sounds like you need to use a trigger set to > fire after the insert on table A > > Alex. > > On Tue, 30 Jan 2001, Nelio Alves Pereira Filho wrote: > > > I read at the docs that rules are executed before the query that > > generated them. Is there any way to change this? > > > > Here's my problem: I have two tables, A and B, and after an INSERT on > > table A, I want to update an specific field on table B, with the result > > of a sum in A. To do this I needed some information about the row just > > inserted, so I used rules. As my sum is running before the insertion, > > its result is wrong. > > > > Any ideas? > > > > Tks > > > > -- > > Nelio Alves Pereira Filho > > IFX Networks - www.ifx.com.br > > +55 11 3365-5863 > > nelio@ifx.com.br > > -- Nelio Alves Pereira Filho IFX Networks - www.ifx.com.br +55 11 3365-5863 nelio@ifx.com.br
Here are some good references on using triggers: http://www.postgresql.org/docs/aw_pgsql_book/node204.html http://www.postgresql.org/users-lounge/docs/7.0/user/c40874113.htm These docs show you how to create a trigger, and use the information contained in the new tuple for calculations.... Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 30 Jan 2001, Nelio Alves Pereira Filho wrote: > Yes, but how can I pass a reference of the row just inserted to the > trigger?? > > adb wrote: > > > > Sounds like you need to use a trigger set to > > fire after the insert on table A > > > > Alex. > > > > On Tue, 30 Jan 2001, Nelio Alves Pereira Filho wrote: > > > > > I read at the docs that rules are executed before the query that > > > generated them. Is there any way to change this? > > > > > > Here's my problem: I have two tables, A and B, and after an INSERT on > > > table A, I want to update an specific field on table B, with the result > > > of a sum in A. To do this I needed some information about the row just > > > inserted, so I used rules. As my sum is running before the insertion, > > > its result is wrong. > > > > > > Any ideas? > > > > > > Tks > > > > > > -- > > > Nelio Alves Pereira Filho > > > IFX Networks - www.ifx.com.br > > > +55 11 3365-5863 > > > nelio@ifx.com.br > > > > > -- > Nelio Alves Pereira Filho > IFX Networks - www.ifx.com.br > +55 11 3365-5863 > nelio@ifx.com.br >
We know we need to change INSERT rules to fire after the command. Hopefully this will be done in 7.2. > I read at the docs that rules are executed before the query that > generated them. Is there any way to change this? > > Here's my problem: I have two tables, A and B, and after an INSERT on > table A, I want to update an specific field on table B, with the result > of a sum in A. To do this I needed some information about the row just > inserted, so I used rules. As my sum is running before the insertion, > its result is wrong. > > Any ideas? > > Tks > > -- > Nelio Alves Pereira Filho > IFX Networks - www.ifx.com.br > +55 11 3365-5863 > nelio@ifx.com.br > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
I wrote a plpgsql function/stored procedure to get around this problem.
I am not sure specifically what information you need about the row
after INSERT, but you could write a function that performs the INSERTs
and summing. After the initial INSERT you can use a SELECT INTO
statement to pass whatever values you are summing and any other values
you might require into function variables. You would then perform your
secondary INSERT using the summed values from the variables. It may not
be the most efficient way to achieve this... but it works.
EXAMPLE:
-- You could specify NULL or BOOL if no
-- value is needed for a return value
CREATE FUNCTION some_function (INTEGER, TEXT) RETURNS INTEGER AS '
DECLARE
arg1 ALIAS FOR $1;
arg2 ALIAS FOR $2;
a1 INTEGER;
a2 INTEGER;
sum INTEGER;
INSERT INTO a VALUES (arg1, arg2);
SELECT INTO a1 a.value_1, a2 a.value2
FROM a
WHERE comparison1 = arg1
AND comparison2 = arg2;
sum := a1 + a2;
INSERT INTO b VALUES (sum);
return sum; -- or bool, NULL if no return vaue is needed
END;'
LANGUAGE 'plpgsql';
Obviously this is a very simplified version of what you are wanting to
do, but it should give you a good idea where to take it. More than
likely you will also want to include a test ISNULL for the variables
being passed values from the SELECT INTO and raise an exception if NULL.
Also, it would be much more effecient to know the PK for the table you
are using for the SELECT INTO rather than selecting on the inserted
values. Another alternative would be using the libpq API and write the
function in C. You can then call the C prog. from inside Postgres.
There is sufficient documentation on this... providing you have a good
foundation writing C code. This is the alternative I eventually chose.
However, my function is process intensive sorting and evaluating on
average over 500K records and growing. If yours is not... the 'plpgsql'
function will more than likely suffice. Good luck... and I hope this
works out for you. And remember, have FUN!!
Regards, Michael
>> I read at the docs that rules are executed before the query that
>> generated them. Is there any way to change this?
>>
>> Here's my problem: I have two tables, A and B, and after an INSERT on
>> table A, I want to update an specific field on table B, with the result
>> of a sum in A. To do this I needed some information about the row just
>> inserted, so I used rules. As my sum is running before the insertion,
>> its result is wrong.
>>
>> Any ideas?
>>
>> Tks
>>
>> --
>> Nelio Alves Pereira Filho
>> IFX Networks - www.ifx.com.br
>> +55 11 3365-5863
>> nelio@ifx.com.br
>>