Hi,
I have a pl/pgSQL function[1] to calculate row numbers (based on [2]).
It uses a custom variable. This was working earlier, but is breaking now
saying it can't recognise the variable. The custom_variable_classes is
set in the postgresql.conf.
pgdb001=> select rownum('') from generate_series(1,10);
ERROR: unrecognized configuration parameter "olap.rownum_name"
pgdb001=> show custom_variable_classes;
custom_variable_classes
-------------------------
olap
(1 row)
I am using PostgreSQL 8.3.3 on CentOS 5.2 x64.
TIA,
Malcolm
[1]
CREATE OR REPLACE FUNCTION rownum(in_code TEXT)
RETURNS INT4
LANGUAGE plpgsql
as $BODY$
DECLARE
current_id TEXT;
current_rownum INT4;
settings_id TEXT;
BEGIN
current_id := statement_timestamp()::TEXT || in_code;
settings_id := current_setting('olap.rownum_name');
IF settings_id IS DISTINCT FROM current_id THEN
PERFORM set_config('olap.rownum_name',
current_id::TEXT, false);
current_rownum := 0;
ELSE
current_rownum :=
current_setting('olap.rownum_count')::INT4;
END IF;
current_rownum := current_rownum + 1;
PERFORM set_config('olap.rownum_count', current_rownum::TEXT,
false);
RETURN current_rownum;
END;
$BODY$;
[2]
<http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/>
