Re: row numbering

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,
>
>