Thread: 8.2 RETURNING functionality in a trigger

Hello,

I have searched unsuccessfully for the answer to this so I hope that one of you may be able to help me.

I am using the new RETURNING functionality but can't make it work in a trigger.

In psql I can run a query like this:
    INSERT INTO foo(col1, col2, col3) VALUES (1, 2, 3) RETURNING col1;
and I get col1 back from the insert.

Now, when I do that in a C trigger via SPI_exec the SPI_exec succeeds, but I can't figure out how to access the RETURNING part.

I have:
if (SPI_exec("INSERT ....", 0) < 0)
{
    // fail
}
else
{
    Datum test = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull);
}

I have used similar code to get values from SELECT without problems.  I assumed that an INSERT RETURNING would place the returned value into the global SPI_tuptable, but it seems that this isn't the case?  If, right after the SPI_exec, I check SPI_tuptable it is indeed NULL. 

I guess my question is then: In a trigger using SPI_exec, how does one get the RETURNING part of the query?

Thank you for any help you could offer,
Doug

"Doug Johnson" <metaphaze@gmail.com> writes:
> In psql I can run a query like this:
>     INSERT INTO foo(col1, col2, col3) VALUES (1, 2, 3) RETURNING col1;
> and I get col1 back from the insert.

> Now, when I do that in a C trigger via SPI_exec the SPI_exec succeeds, but I
> can't figure out how to access the RETURNING part.

Works for me; I suspect the problem is in some part of your code that
you didn't show us.  Is it really just a plain INSERT/RETURNING (no
possible interference from rules, say)?  Are you really only testing the
SPI_exec result code for negative (maybe you forgot to allow
SPI_OK_INSERT_RETURNING as a valid result)?

            regards, tom lane