trying to write a function to do the following: 1. select a random *unused* (see below) row from a table. 2. select 9 more rows from same table based on relation to first row selected 3. mark these 10 rows as used and assign a group 4. goto 1 5 when all rows are used, return the set of rows with the group
$BODY$ DECLARE myrec record; tyrec record; qtxt text; pc int; BEGIN grp := 0; update buky2 set flag='f'; --set everything as unused select into pc count(*) from buky2 where flag = 'f'; -- get total row count LOOP EXIT WHEN pc < 0; FOR myrec IN select * from buky2 where flag = 'f' order by random() limit 1 LOOP --get the one random *unused* row qtxt := 'select a.premises,b.premises as thisprem, distance(a.transform,b.transform),b.transform from buky2 a ' ||'inner join buky2 b on expand(a.transform,9009) && expand(b.transform,9009) ' ||'where a.flag = '||quote_literal('f')||' AND a.premises = '||quote_literal(myrec.premises)||' order by distance(a.transform,b.transform) limit 10'; grp := grp + 1; FOR tyrec in EXECUTE qtxt LOOP --this should have 10 rows here update buky2 set flag = 't' where premises = tyrec.thisprem; pc := pc - 1; premises := tyrec.thisprem; geo := tyrec.transform; RETURN NEXT; END LOOP; END LOOP; END LOOP; END;
the problem is that im getting non-unique/duplicate premises.any suggestions?