[DBIx-Class-Devel] UPDATE RETURNING

[Dagfinn Ilmari Mannsåker]

Peter Rabbitson <rabbit+dbic at rabbit.us> writes:

> On Mon, Jul 15, 2013 at 10:46:55AM -0500, fREW Schmidt wrote:
>> As for my "lazy" comment, imagine we supported the following:
>> 
>>    say $_->name for $rs->search({
>>       id => {
>>          -in => $rs->update_returning({
>>             foo => 'bar'
>>          }, {
>>             columns => 'id', # or maybe better yet ->get_column('id')
>>          })->as_query
>>       }
>>    })->all
>> 
>> So update_returning returns an unfetched resultset, just like search
>> does.
>
> I don't think many engines support this (may very well be Pg only, 
> verify) - but afaik RETURNING does not yield to being treated as a 
> subselect. That is its values are only available to be shoved in a 
> cursor.

Pg does not support this, but as of version 9.1 you can do the
equivalent as a writable CTE:

    WITH updated AS (UPDATE mytable SET foo = 'bar' RETURNING id)
    SELECT * FROM othertable
    WHERE id IN (SELECT id FROM updated);

<http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING>

-- 
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law