Обсуждение: 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;
Re: workaround for missing ROWNUM feature with the help of GUC variables
Hello,
here my two pence on this recurring thema.
(just a workaround)
I don't understand what you mean by missing ROWNUM feature, PG got this with windows-functions in 8.4:
http://www.postgresql.org/docs/8.4/interactive/functions-window.html
Example:
select username, row_number() over() from my_user_table;
username | row_number
-------------+------------
admin | 1
everyone | 2
-- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / CTO ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Rosenholmveien 25 | know how to do a thing and to watch | 1414 Trollåsen | somebody else doing it wrong, without | NORWAY | comment. | | | Tlf: +47 24 15 38 90 | | Fax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
> I don't understand what you mean by missing ROWNUM feature, PG got this with windows-functions in 8.4:
> http://www.postgresql.org/docs/8.4/interactive/functions-window.html
I'm unfortunately still using 8.3.
sorry not to have mentioned that.
Marc Mamin