From: Tom Lane <tgl@sss.pgh.pa.us>
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: need ``row number``
Date: Fri, 24 Sep 2004 10:20:13 -0400
Message-ID: <21339.1096035613@sss.pgh.pa.us> (raw)
In-Reply-To: <20040924092117.A1195@hermes.hilbert.loc>
References: <s1530b77.094@mail.tpchd.org>
<20040924092117.A1195@hermes.hilbert.loc>
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> I am not convinced I'll need a SRF. I am not trying to
> calculate something that isn't there yet. I am just trying to
> join two views appropriately. I might have to employ some
> variant of Celko's integer helper table but I'm not sure how
> to proceed.
A fairly common hack for this is to use a sequence:
create temp sequence tseq;
select nextval('tseq'), * from (select .... order by ...) ss;
Note you must do any desired ORDER BY inside the subselect. If it's
outside then it happens after computation of the row numbers, which
is exactly not what you want.
The major limitation of this is that you need to do some auxiliary
operations to create or reset the semaphore before each query.
There are some related hacks in the archives that use PL functions
with private persistent state, instead of a sequence object.
regards, tom lane
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: tgl@sss.pgh.pa.us, Karsten.Hilbert@gmx.net, pgsql-general@postgresql.org
Subject: Re: need ``row number``
In-Reply-To: <21339.1096035613@sss.pgh.pa.us>
* 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