From: Dave Gomboc <dave@boyne.cs.ualberta.ca>
Subject: select from update from select?
Date: 16 Feb 2003 12:16:56 GMT
Organization: University of Alberta, Edmonton, Canada
Lines: 40
Message-ID: <b2nvfo$9ro$1@pulp.srv.ualberta.ca>
User-Agent: tin/1.4.2-20000205 ("Possession") (UNIX) (Linux/2.4.9-31uofa
	(i686))
To: pgsql-sql@postgresql.org

I'm not sure if this is the correct place for this question.  If it
isn't, I'd appreciate a pointer to a better.

Here is my pseudo-SQL (formatted for readability):

select work_queue_id from
    (update work_queue_table set worker_id = 5,
                                 reservation_time = 'now()'
                             where work_queue_id in
        (select work_queue_id from work_queue, optimization_task_table
                              where reservation_time is null
                                and concordance is null
                              order by priority descending limit 1
        )
    )

I am trying to do the following (using PostgreSQL 7.3.2):

1. reserve a row (which represents some work to be done) in my
work_queue_table based on certain conditions (a: that work hasn't
already been reserved; b: there is no work considered to be of higher
priority than it available)

2. get the primary key (work_queue_id) of that reserved row.

I'm wondering what changes I would need to make to my pseudo-SQL to
make it legitimate.  I'm trying to avoid a two-step process of
attempting to reserve a row, then checking to see which -- if any --
row was actually reserved.

If it matters, I'm writing my code in C++, and using libpqxx (1.4.1)
to interface to the database.

Dave

-- 
Dave Gomboc
M.Sc. Student                                1-41 Athabasca Hall
Department of Computing Science              Edmonton, Alberta,
University of Alberta                        Canada  T6G 2E5