Re: Order of Rules - Mailing list 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
>>