Re: Order of Rules
От | Michael Dunn |
---|---|
Тема | Re: Order of Rules |
Дата | |
Msg-id | 3A79B28F.202@2cactus.com обсуждение исходный текст |
Ответ на | Re: Order of Rules (Bruce Momjian <pgman@candle.pha.pa.us>) |
Список | pgsql-general |
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 >>
В списке pgsql-general по дате отправления:
Следующее
От: elwood@agouros.de (Konstantinos Agouros)Дата:
Сообщение: Is Oracle really so much faster