workaround for missing ROWNUM feature with the help of GUC variables

Hello,

here my two pence on this recurring thema.

(just a workaround)

regards,

Marc Mamin

The PG parameter must be set to allow defining own configuration
variables:

#-----------------------------------------------------------------------
---

# CUSTOMIZED OPTIONS

#-----------------------------------------------------------------------
---

custom_variable_classes = 'public' # list of custom
variable class names

usage example:

--------------------

select my_rownum(),* from generate_series (10,15);

wrong usage:

--------------------

select my_rownum() as n1,

my_rownum() as n2,

*

from generate_series (10,15);

solution:

--------------------

select my_rownum('1') as n1,

my_rownum('2') as n2,

*

from generate_series (10,15);

Code:

=====

CREATE OR REPLACE FUNCTION public.my_rownum ()

returns int AS

$BODY$

/*

equivalent to oracle rownum

(The previous row value is attached to a GUC Variable valid in the
current transaction only)

quite slow :-(

*/

DECLARE

current_rownum int;

config_id varchar = 'public.my_rownum';

BEGIN

BEGIN

current_rownum := cast (current_setting (config_id) as int);

EXCEPTION when others then

return cast( set_config(config_id, cast(1 as text), true) as int);

END;

RETURN cast( set_config(config_id, cast(current_rownum + 1 as text),
true) as int);

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

/*
------------------------------------------------------------------------
------------------

For multiple usage:

------------------------------------------------------------------------
------------------ */

CREATE OR REPLACE FUNCTION public.my_rownum ( id varchar )

returns int AS

$BODY$

/*

equivalent to oracle rownum

quite slow :-(

(The previous row value is attached to a GUC Variable valid in the
current transaction only)

$1: when more than one my_rownum is used within a query, each call
must have its own ID in order to get different GUC variable).

*/

DECLARE

current_rownum int;

config_id varchar = 'public.my_rownum'||id;

BEGIN

BEGIN

current_rownum := cast (current_setting (config_id) as int);

EXCEPTION when others then

return cast( set_config(config_id, cast(1 as text), true) as int);

END;

RETURN cast( set_config(config_id, cast(current_rownum + 1 as text),
true) as int);

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;