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;
