Hi folks,
I'm puzzling over whether it is possible within SQL alone to determine
the ordinal position of a row within the set returned by a query. It
seems clear to me that pgsql "knows" what position in a set a
particular tuple holds, since one can OFFSET, ORDER BY, and LIMIT;
however, I can't seem to find a function or "hidden field" that will
return this.
What I would like is something along these lines: I wish to ORDER BY
an ordinal field that is likely to be present, but may not be present,
and then by a unique value to ensure stability of ordering. Since the
ordinal may be absent, I'd like a running total on the side: For
example:
guys
name age
-------------------
bob 33
charlie 35
doug 28
ed 33
select name, age, running_total() from guys order by age, name
name age running_total()
-----------------------------------------
doug 28 1
bob 33 2
ed 33 3
charlie 35 4
I think I could do this by means of creating a temporary table within a
plpgsql function, but that seems awful heavy-duty. I have an inkling
that there may be an existing pg_ function or field, like oid, which I
might call to get this info. Thoughts?
Regards,
Randall
