Skip site navigation (1) Skip section navigation (2)

8.2 RETURNING functionality in a trigger

From: "Doug Johnson" <metaphaze(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: 8.2 RETURNING functionality in a trigger
Date: 2007-07-07 21:21:10
Message-ID: b82f5ea00707071421p52f4765ak52f2509e2d701a27@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2007-07-07 21:46:42
Subject: Re: 8.2 RETURNING functionality in a trigger
Previous:From: Viatcheslav KalininDate: 2007-07-06 16:41:37
Subject: Re: distinct doesn't work

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group