SEO

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,


--
Sinceramente,
Josué Maldonado.

.... "El bien supone la luz, el mal tinieblas. Cada vez hay menos
iluminación."

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

josue wrote:
> is there a way return a column with the row number automatically
> generated according the way the rows were processed by the query.

No, but you can easily keep a counter in the client.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

If you insert the results of your query into a table with a serial
column, the serial column will do what you want..

On Sat, 2005-02-26 at 01:10 +0100, Peter Eisentraut wrote:
> josue wrote:
> > is there a way return a column with the row number automatically
> > generated according the way the rows were processed by the query.
>
> No, but you can easily keep a counter in the client.
>
--
Mike Harding <mvh@ix.netcom.com>


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

josue <josue@lamundial.hn> writes:

> to something like:
>
> select counter(),a,b from foo;

The OLAP SQL Standard way to spell this is "ROW_NUMBER() OVER ()".

Postgres doesn't have any support for any of the OLAP features however. It
would be really nice because they're nigh impossible to emulate with standard
SQL.

You might be able to get away with a sequence, but if you have multiple
connections running this query at the same time then that will be awkward. You
would have to create a new sequence for the query every time.

If you can you're probably best off doing this in the client. You can do any
sort of level break logic you need in the client, and even refer to previous
records. The only thing that would make doing it in the client awkward would
be if you were planning to use the results in more query logic such as a join.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

Jeff Davis wrote:
> 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.

Thanks, all the ideas you and the other members gave me have been very
helpfully


--
Sinceramente,
Josué Maldonado.

.... "Ser fiel supone engañar a tu pareja en el momento justo"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Fri, 2005-02-25 at 16:44, 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

You could use a temporary sequence:

create temporary sequence counter;
select nextval('counter'),* from sometable;

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

> > is there a way return a column with the row number automatically
> > generated according the way the rows were processed by the query.
>
> No, but you can easily keep a counter in the client.
How, then, do I do it if I "need" the "row number" in a view ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

NTPT wrote:
> Having some sort of line numbering in result query would be nice...

The query result has line numbering. How else are you accessing the
individual rows?

Is the issue really that you want psql to number the lines on display?
That could be implemented.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

> If you insert the results of your query into a table with a serial
> column, the serial column will do what you want..

Plus add a huge overload... ?

Having some sort of line numbering in result query would be nice...





----- Original Message -----
From: "Mike Harding" <mvh@ix.netcom.com>
To: "Peter Eisentraut" <peter_e@gmx.net>
Cc: "josue" <josue@lamundial.hn>; <pgsql-general@postgresql.org>
Sent: Saturday, February 26, 2005 1:27 AM
Subject: Re: [GENERAL] row numbering


> If you insert the results of your query into a table with a serial
> column, the serial column will do what you want..
>
> On Sat, 2005-02-26 at 01:10 +0100, Peter Eisentraut wrote:
>> josue wrote:
>> > is there a way return a column with the row number automatically
>> > generated according the way the rows were processed by the query.
>>
>> No, but you can easily keep a counter in the client.
>>
> --
> Mike Harding <mvh@ix.netcom.com>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly