From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: slow query execution
Date: Wed, 30 May 2007 13:39:18 -0400
Message-ID: <20070530173918.GN16260@phlogiston.dyndns.org> (raw)
In-Reply-To: <710412.41081.qm@web52709.mail.re2.yahoo.com>
References: <710412.41081.qm@web52709.mail.re2.yahoo.com>
On Wed, May 30, 2007 at 10:03:16AM -0700, Trigve Siver wrote:
> Hi, Thanks for reply, As you have mentioned I need to get row
> numbers for my query, so when I make some other query with same
> data I will know which row number has a particular ID.
Oh, wait. If _that's_ your plan, then this will never work. The
data could change, and your row numbers would come out wrong.
What do you need "row numbers" for anyway? The very idea is inimical
to SQL, because the data is fundamentally unordered.
> As you
> mentioned "You can do this with a temporary sequence, among other
> approaches...". Can you point me to some sources or give me some
> examples, please?
BEGIN;
CREATE SEQUENCE temp_seq;
SELECT nextval('temp_seq'), other stuff from table;
DROP SEQUENCE temp_seq;
COMMIT/ROLLBACK;
If you only select, you don't have to do the DROP, you just ROLLBACK.
I think there's some nifty way to get generate_series to do this too,
but I don't know it offhand (generating row numbers sounds to me like
a bad idea, so I don't do it).
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ajs@crankycanuck.ca
Subject: Re: slow query execution
In-Reply-To: <20070530173918.GN16260@phlogiston.dyndns.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox