Hi all,
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
seems pretty straight forward. annotated code below
$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?
Rhys
