
Zitat von
akretschmer
Heute hab ich mal eine Frage:
Gegeben sei eine Tabelle:
Hat da jemand eine gute Idee?
Andreas
Okay. Ich hab es.
Diese Funktion
Code:
create or replace function do_tasks(in int, out t text) returns setof text as $$
declare
r record;
m record;
begin
delete from jobs;
insert into jobs (id, duration) select generate_series(1,$1),0;
for r in select id, duration from tasks order by duration desc loop
select into m id, sum(coalesce(duration,0)) from jobs group by id order by sum(duration) asc limit 1;
insert into jobs (id, job, duration) values (m.id, r.id, r.duration);
end loop;
delete from jobs where job is null;
return next ;
end;
$$ language plpgsql; fülls diese Tabelle:
Code:
test=# \d jobs
Table "public.jobs"
Column | Type | Modifiers
----------+---------------+-----------
id | integer |
job | text |
duration | numeric(10,2) | Tests:
Code:
test=*# select * from do_tasks(4);
t
---
(1 row)
test=*# select id, sum(duration) from jobs group by id;
id | sum
----+-------
4 | 31.00
3 | 31.00
2 | 30.00
1 | 30.00
(4 rows)
test=*# select * from do_tasks(7);
t
---
(1 row)
test=*# select id, sum(duration) from jobs group by id;
id | sum
----+-------
7 | 18.00
6 | 17.00
5 | 17.00
4 | 18.00
3 | 18.00
2 | 17.00
1 | 17.00
(7 rows)
Noch etwas hübsch machen, fertig.
Andreas
Lesezeichen