From: | Jeff Davis <jdavis-pgsql(at)empires(dot)org> |
---|---|
To: | josue <josue(at)lamundial(dot)hn> |
Cc: | PgSQL General List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: row numbering |
Date: | 2005-02-26 08:35:30 |
Message-ID: | 1109406931.4089.211.camel@jeff |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Here's an example using plperl and global variables. The variables are
local to a session so you don't have to worry about the counters
interfering. If you need two counters in a session, just execute
reset_counter().
CREATE OR REPLACE FUNCTION reset_counter() RETURNS INT AS $$
$_SHARED{counter} = 0;
return 0;
$$ LANGAUGE plperl;
CREATE OR REPLACE FUNCTION counter() RETURNS INT AS $$
return $_SHARED{counter}++;
$$ LANGUAGE plperl;
Now, you can execute the queries just like you want:
select counter(),a,b from foo;
There are a couple trivial issues, like you can start from 1 instead of
0 if you want.
Regards,
Jeff Davis
On Fri, 2005-02-25 at 16:44 -0600, josue wrote:
> Hello list,
>
> is there a way return a column with the row number automatically
> generated according the way the rows were processed by the query.
>
> For instance:
> select a,b from foo;
> a b
> 20 yes
> 40 no
> 15 yes
>
> to something like:
>
> select counter(),a,b from foo;
> counter a b
> 1 20 yes
> 2 40 no
> 3 15 yes
>
> Thanks in advance,
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2005-02-26 09:27:55 | Re: postgresql 8.0 advantages |
Previous Message | Greg Stark | 2005-02-26 05:45:35 | Re: Hash aggregates blowing out memory |