I had a brief look at DB2's row_number function which seems to be pretty 
useful.
What it does is:

test=# SELECT row_number(), relname FROM pg_class LIMIT 3;
  row_number |    relname
------------+----------------
           1 | pg_description
           2 | pg_group
           3 | pg_proc
(3 rows)

This makes sense to me and I need this feature from time to time. My 
question is: How do I find out when a query starts? Inside a table 
function I can call SRF_IS_FIRSTCALL() to see when it is called first. 
Is there an easy way to check that inside an "ordinary" C function 
returning just one value?
Currently my function counts the number of times it has been called per 
connection. I could write a second function for resetting the counter 
but this is not too smart ...

	Regards,

		Hans


-- 
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at



Thu, 17 Jul 2003 17:03:03 +0200
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at>
DB2's row_number()
Here is how I doi it when I need to...

BEGIN;
CREATE TEMP SEQUENCE row_num;
SELECT next_val('row_num'), relname FROM pg_class LIMIT 3;
ROLLBACK;

You could also do this with a Set Returning Fucntion so that it returns the 
nextval in a simular way



On Thursday 17 July 2003 08:03, Hans-Jürgen Schönig wrote:
> I had a brief look at DB2's row_number function which seems to be pretty
> useful.
> What it does is:
>
> test=# SELECT row_number(), relname FROM pg_class LIMIT 3;
>   row_number |    relname
> ------------+----------------
>            1 | pg_description
>            2 | pg_group
>            3 | pg_proc
> (3 rows)
>
> This makes sense to me and I need this feature from time to time. My
> question is: How do I find out when a query starts? Inside a table
> function I can call SRF_IS_FIRSTCALL() to see when it is called first.
> Is there an easy way to check that inside an "ordinary" C function
> returning just one value?
> Currently my function counts the number of times it has been called per
> connection. I could write a second function for resetting the counter
> but this is not too smart ...
>
> 	Regards,
>
> 		Hans

-- 
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com

Thu, 17 Jul 2003 11:16:18 -0700
Darcy Buskermolen <darcy@wavefire.com>
Re: DB2's row_number()
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:
> question is: How do I find out when a query starts? Inside a table 
> function I can call SRF_IS_FIRSTCALL() to see when it is called first. 
> Is there an easy way to check that inside an "ordinary" C function 
> returning just one value?

Use fn_extra to store some state data.
pg_stat_get_backend_idset() is a useful example.

			regards, tom lane

Thu, 17 Jul 2003 14:30:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: DB2's row_number() 