workaround for missing ROWNUM feature with the help of GUC variables - Mailing list pgsql-sql
| From | Marc Mamin |
|---|---|
| Subject | workaround for missing ROWNUM feature with the help of GUC variables |
| Date | |
| Msg-id | C4DAC901169B624F933534A26ED7DF31034BB74D@JENMAIL01.ad.intershop.net Whole thread Raw |
| Responses |
Re: workaround for missing ROWNUM feature with the help of
GUC variables
|
| List | pgsql-sql |
<div class="Section1"><p class="MsoNormal"><span lang="EN-US">Hello,</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">here my two pence on this recurring thema.</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">(just a workaround)</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">regards,</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">Marc Mamin</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">The PG parameter must be set to allow defining own
configurationvariables:</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">
#--------------------------------------------------------------------------</span><pclass="MsoNormal"><span
lang="EN-US"> # CUSTOMIZED OPTIONS</span><p class="MsoNormal"><span lang="EN-US">
#--------------------------------------------------------------------------</span><pclass="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US"> custom_variable_classes = 'public' #
listof custom variable class names</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">usage example:</span><p class="MsoNormal"><span
lang="EN-US">--------------------</span><pclass="MsoNormal"><span lang="EN-US">select my_rownum(),* from
generate_series(10,15);</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US">wrongusage:</span><p class="MsoNormal"><span lang="EN-US">--------------------</span><p
class="MsoNormal"><spanlang="EN-US">select my_rownum() as n1,</span><p class="MsoNormal"><span lang="EN-US">
my_rownum()as n2,</span><p class="MsoNormal"><span lang="EN-US"> *</span><p class="MsoNormal"><span
lang="EN-US"> from generate_series (10,15);</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US">solution: </span><p class="MsoNormal"><span
lang="EN-US">--------------------</span><pclass="MsoNormal"><span lang="EN-US">select my_rownum('1') as n1,</span><p
class="MsoNormal"><spanlang="EN-US"> my_rownum('2') as n2,</span><p class="MsoNormal"><span lang="EN-US">
*</span><pclass="MsoNormal"><span lang="EN-US"> from generate_series (10,15);</span><p class="MsoNormal"><span
lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">Code:</span><p class="MsoNormal"><span
lang="EN-US">=====</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">CREATE
ORREPLACE FUNCTION public.my_rownum ()</span><p class="MsoNormal"><span lang="EN-US">returns int AS</span><p
class="MsoNormal"><spanlang="EN-US">$BODY$</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US">/*</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> equivalent to oracle rownum</span><p class="MsoNormal"><span lang="EN-US"> (The
previousrow value is attached to a GUC Variable valid in the current transaction only)</span><p class="MsoNormal"><span
lang="EN-US"> quite slow :-(</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US">*/ </span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US">DECLARE</span><pclass="MsoNormal"><span lang="EN-US"> current_rownum int;</span><p
class="MsoNormal"><spanlang="EN-US"> config_id varchar = 'public.my_rownum';</span><p class="MsoNormal"><span
lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">BEGIN</span><p class="MsoNormal"><span lang="EN-US">
</span><pclass="MsoNormal"><span lang="EN-US"> BEGIN</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> current_rownum := cast (current_setting (config_id) as int);</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> EXCEPTION when others then
</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> return cast(
set_config(config_id,cast(1 as text), true) as int);</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> END;</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> RETURN cast( set_config(config_id, cast(current_rownum + 1 as text), true) as
int);</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">END;</span><p
class="MsoNormal"><spanlang="EN-US">$BODY$</span><p class="MsoNormal"><span lang="EN-US"> LANGUAGE 'plpgsql'
VOLATILE;</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US">/*
------------------------------------------------------------------------------------------</span><p
class="MsoNormal"><spanlang="EN-US"> For multiple usage:</span><p class="MsoNormal"><span lang="EN-US">
------------------------------------------------------------------------------------------*/ </span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">CREATE OR REPLACE FUNCTION
public.my_rownum( id varchar )</span><p class="MsoNormal"><span lang="EN-US">returns int AS</span><p
class="MsoNormal"><spanlang="EN-US">$BODY$</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> /*</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> equivalent to oracle rownum</span><p class="MsoNormal"><span lang="EN-US"> quite
slow:-(</span><p class="MsoNormal"><span lang="EN-US"> (The previous row value is attached to a GUC Variable valid in
thecurrent transaction only)</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US"> $1: when more than one my_rownum is used within a query, each call must have its own ID in order to get
differentGUC variable).</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US"> */</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US">DECLARE</span><pclass="MsoNormal"><span lang="EN-US"> current_rownum int;</span><p
class="MsoNormal"><spanlang="EN-US"> config_id varchar = 'public.my_rownum'||id;</span><p class="MsoNormal"><span
lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> BEGIN</span><p class="MsoNormal"><span lang="EN-US">
</span><pclass="MsoNormal"><span lang="EN-US"> BEGIN</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> current_rownum := cast (current_setting (config_id) as int);</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> EXCEPTION when others then
</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> return cast(
set_config(config_id,cast(1 as text), true) as int);</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> END;</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> RETURN cast( set_config(config_id, cast(current_rownum + 1 as text), true) as
int);</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> END;</span><p
class="MsoNormal"><spanlang="EN-US"> $BODY$</span><p class="MsoNormal"><span lang="EN-US"> LANGUAGE 'plpgsql'
VOLATILE;</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US"> </span></div>