On 4/27/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> how about this:
> create table job(job_id int, [...])
> create sequence worker;
>
couple typos: here is an example that works:
create table job(job_id serial);
create sequence worker;
-- get next available job
create function next_job() returns job as
$$
select job from job join
(
select nextval('worker') as requested_job
) q on job_id = requested_job
and
(
(select (w.last_value, w.is_called) < (j.last_value, j.is_called)
from worker w, job_job_id_seq j)
);
$$ language sql;
select next_job();
again, remembering that sequences are not rolled back on transaction
failure, you have to think really carefully about failure conditions
before going with something like this.
merlin