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,
>
>
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
