Обсуждение: returning results from an update for joining other tables

Hi!

I have a table containing data and a column which holds information on
which compute-node processes the data. In a given interval I'd like to
request some data from this table and mark these returned rows by
setting the "process_node" column to the node-name, which asked for data.
There may also be rows which have the column process_node set to the
name of the node currently asking.

What I tried was something like this, which gave me a syntax error:

SELECT * FROM
( UPDATE taSET process_node='nodename'WHERE a>10 AND process_node is nullRETURNING *
) AS ta
JOIN someothertable ON ...

Can I somehow select some rows and do multiple operations on exactly
this resultset?
In my case update columns, then join columns from other tables and then
return the resultset with the joined columns?

Regards
Patrick

am  Tue, dem 10.06.2008, um 10:50:52 +0200 mailte Patrick Scharrenberg folgendes:
> Hi!
> 
> I have a table containing data and a column which holds information on
> which compute-node processes the data. In a given interval I'd like to
> request some data from this table and mark these returned rows by
> setting the "process_node" column to the node-name, which asked for data.
> There may also be rows which have the column process_node set to the
> name of the node currently asking.
> 
> What I tried was something like this, which gave me a syntax error:
> 
> SELECT * FROM
> ( UPDATE ta
>     SET process_node='nodename'
>     WHERE a>10 AND process_node is null
>     RETURNING *
> ) AS ta
> JOIN someothertable ON ...

It's a know limitation, see <48468922.9030108@enterprisedb.com>


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Hi!
>> What I tried was something like this, which gave me a syntax error:
>>
>> SELECT * FROM
>> ( UPDATE ta
>>     SET process_node='nodename'
>>     WHERE a>10 AND process_node is null
>>     RETURNING *
>> ) AS ta
>> JOIN someothertable ON ...
>
> It's a know limitation, see <48468922.9030108@enterprisedb.com>

Oh, I see.

Are there ways to work around this limitation?

Patrick

Hi!
>> >> What I tried was something like this, which gave me a syntax error:
>> >>
>> >> SELECT * FROM
>> >> ( UPDATE ta
>> >>     SET process_node='nodename'
>> >>     WHERE a>10 AND process_node is null
>> >>     RETURNING *
>> >> ) AS ta
>> >> JOIN someothertable ON ...
> >
> > It's a know limitation, see <48468922.9030108@enterprisedb.com>

Oh, I see.

Are there ways to work around this limitation?

Patrick