Hi,

Following directly on from my previous question on the list - can
anyone help me with why this is going wrong:

I have a largish table (behr_grid: 54720 rows) and I need to maintain a
table (spotlocs) of the top 20 (see previous post!) rows currently
meeting various criteria within each of 8 major groups (realm_id) in
the original table. The table spotlocs (20*8 = 160 rows) has columns of
the form:

realm_id
spot_num
top_20_rows_in_behr_grid_meeting_criterion_1
top_20_rows_in_behr_grid_meeting_criterion_2

The general idea is to use a plpgsql function (thanks again, Sean) to
get the top 20 for each realm_id. I then need to update spotlocs with
the current set of top rows but I need to bring in a spot_num in order
to match the updates by realm_id and spot_num. If I just use the
following code then the first grid_id for each realm is recycled into
spotlocs (all 20 rows for each realm get the same row)

update spotlocs
set r_rand = currspots.grid_id
from (select grid_id, realm_id from get_top_20()) as currspots
where spotlocs.realm_id = currspots.realm_id;

So, I created a temporary sequence to allocate spot_num values (note
that the order within realm_id values is arbitrary) and bolt that in to
the top 20 list. Because I'm always selecting in blocks of 20, I can
get away with cycling the sequence.

create temporary sequence spot_num_seq start 1 maxvalue 20 increment 1
cycle;

update spotlocs
set r_rand = currspots.grid_id
from ( select tmp.grid_id, nextval('spot_num_seq') as spot_num,
tmp.realm_id from
(select grid_id, realm_id from get_top_20()) as tmp
) as currspots
where spotlocs.realm_id = currspots.realm_id
and spotlocs.spot_num = currspots.spot_num;

This doesn't work - yet separating the subquery out as a create as and
then running the update separately works...

create temporary table currspots as
select grid_id, nextval('spot_num_seq') as spot_num, realm_id
from (select grid_id, realm_id from get_top_20()) as tmp;

update spotlocs set r_rand = currspots.grid_id
where spotlocs.realm_id = currspots.realm_id
and spotlocs.spot_num = currspots.spot_num;

What am I missing? Not that this is a huge problem but I'm just puzzled
as to why the combined approach fails.

Thanks in advance,
David


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly