Inserting a select statement result into another table

Andrew Selle <aselle@upl.cs.wisc.edu>

From: Andrew Selle <aselle@upl.cs.wisc.edu>
To: pgsql-hackers@postgresql.org
Date: 2000-08-17T18:05:17Z
Lists: pgsql-hackers
Alright.  My situation is this.  I have a list of things that need to be done
in a table called tasks.  I have a list of users who will complete these tasks.
I want these users to be able to come in and "claim" the top 2 most recent tasks
that have been added.  These tasks then get stored in a table called todolist
which stores who claimed the task, the taskid, and when the task was claimed.
For each time someone wants to claim some number of tasks, I want to do something
like

INSERT INTO todolist 
	SELECT taskid,'1',now() 
	FROM tasks 
	WHERE done='f' 
	ORDER BY submit DESC 
	LIMIT 2;

Unfortunately, when I do this I get 
ERROR:  ORDER BY is not allowed in INSERT/SELECT

The select works fine

aselle=> select taskid,'1',now() FROM tasks WHERE done='f' ORDER BY submit DESC LIMIT 2;
 taskid | ?column? |          now           
 --------+----------+------------------------
       4 | 1        | 2000-08-17 12:56:00-05
       3 | 1        | 2000-08-17 12:56:00-05
(2 rows)

It seems to me, this is something I should do.  I was wondering if there
is any reason why I can't do this?  I've thought of a couple of workarounds
but they don't seem to be very clean:

1. Read the results of the select at the application level and reinsert into the
    todolist table

2. Add two fields to the task table that keep track of userid and claimed.
   This unfortunately clutters the main task table, and it loses the ability
    to assign multiple people to the same task. It also requires looping at the
    application level I think

3. use a temporary table with a SELECT INTO statement and then copy the contents
   of the temporary table into the table I want it in todolist

Below are the table creation statements for this sample...

-Andy


CREATE TABLE tasks (
	taskid	int4,
	title	varchar(64),
	descr	text,
	submit	datetime,
	done	boolean
);

CREATE TABLE users (
	userid	int4,
	name	varchar(32)
);

CREATE TABLE todolist (
	taskid	int4,
	userid	int4,
	claimed	datetime
);