From: | "Rhys Stewart" <rhys(dot)stewart(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | help with function |
Date: | 2007-06-15 16:12:21 |
Message-ID: | 189966030706150912x78cdd246m92d223d9cf7bc1a2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.anysuggestions?
Rhys
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-06-15 16:16:53 | Re: Another conversion from ASA to PostGres how to |
Previous Message | MC Moisei | 2007-06-15 16:09:49 | Re: Postmaster processes taking all the CPU |