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;