To: pgsql-performance@postgresql.org
From: Hassan Adekoya <hechy_man@yahoo.com>
Subject: increment Rows in an SQL Result Set postgresql
Date: Sat, 15 Jul 2006 17:46:40 +0000 (UTC)
Lines: 44
Message-ID: <loom.20060715T194555-176@post.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: Loom/3.14 (http://gmane.org/)

HI,

1. I have a function that given two parameter produces an arbitrary id, and
text. However arbitrary the id and text are, they are in certain order. i.e. it
is imperative that whatever processing I do, the order is preserved.

2. An odd thing happens when I perform a join on the result set such that the
order that I hope to preserved in destroyed. The same result set but different
ordering. I gather this is due to the query planner. Enough said.

I was hoping to insert a counter in the select query of 1. such that when I
perform the join of 2, I can order by the counter. 


i.e. 

1. select id, astext from function1(1,2) 
2. select id, astext, table2.name from function1(1,2) as tmp, table2 where
tmp.id = table2.id
  
when I perform 1., I get something of sort

id | astext
2  | abc
6  | efg
3  | fhg

I will like to preserve ordering....

When I perform 2, I get somthing of sort

id  | astext | table2.name
6   | efg    | joe
2   | abc    | zyi
3   | fgh    | mec

Can someone help such that I get something like 

id  | astext | table2.name | increment
6   | efg    | joe         | 2
2   | abc    | zyi         | 1
3   | fgh    | mec         | 3

Thanks!