X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id CD4ECD1B4BB for ; Sat, 13 Dec 2003 23:48:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 51740-09 for ; Sat, 13 Dec 2003 19:47:52 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 89A23D1B467 for ; Sat, 13 Dec 2003 19:47:43 -0400 (AST) Received: (qmail 30782 invoked by uid 500); 13 Dec 2003 23:47:32 -0000 Date: Sat, 13 Dec 2003 17:47:32 -0600 From: Bruno Wolff III To: Nathaniel Price Cc: "scott.marlowe" , pgsql-general@postgresql.org, pgsql-php@postgresql.org Subject: Re: Reordering results for a report Message-ID: <20031213234732.GA30707@wolff.to> Mail-Followup-To: Nathaniel Price , "scott.marlowe" , pgsql-general@postgresql.org, pgsql-php@postgresql.org References: <3FDA43E2.5090902@tesseract.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3FDA43E2.5090902@tesseract.net> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/746 X-Sequence-Number: 54140 On Fri, Dec 12, 2003 at 14:40:34 -0800, Nathaniel Price wrote: > Sorry, that's not really what I'm looking for. When I said that the sort > order could be arbitrary, I meant /arbitrary/. As in "no amount of ORDER > BYs will save me from this one" arbitrary; the records could be in any > order the user specifies. > > The idea is that in PHP I'd be using an array to keep track of the > custom order that the results should be returned in, using the array > index to store the sorting order and the values to store the table's > primary key, like so (note that this isn't the code I'm using, it's just > an example): > > $sort[0] = 3; //First record to return (3 is the primary key of the record) > $sort[1] = 1; //Second record to return > $sort[2] = 2; //Third record to return > ... //and so on. > ?> It would be possible to use this array in a query, but this wouldn't work well for large data sets as you would be sending the whole array back and forth a lot. Presumably the reason you want to do the sort in the database is because the dataset is large enough that you don't want to buffer the data in PHP in the first place. I think the temp table solution won't be that simple either. You will need connection pooling and some way to keep requests from the same web session using the same postgres session. You may want to use regular tables which are named by session id and the have some clean up process that removes tables that are no longer needed.