Thread: workaround for missing ROWNUM feature with the help of GUC variables
<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>
Re: workaround for missing ROWNUM feature with the help of GUC variables
From
Andreas Joseph Krogh
Date:
On 08/04/2010 12:35 PM, Marc Mamin wrote: <blockquote
cite="mid:C4DAC901169B624F933534A26ED7DF31034BB74D@JENMAIL01.ad.intershop.net"type="cite"><style>
<!--/* Font Definitions */@font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}/* Style Definitions */p.MsoNormal, li.MsoNormal,
div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink{mso-style-priority:99;color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:purple;text-decoration:underline;}
span.EmailStyle17{mso-style-type:personal-compose;font-family:"Calibri","sans-serif";color:windowtext;}
.MsoChpDefault{mso-style-type:export-only;}
@page Section1{size:612.0pt 792.0pt;margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.Section1{page:Section1;}
--> </style><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><br
/></div></blockquote><br/> I don't understand what you mean by missing ROWNUM feature, PG got this with
windows-functionsin 8.4:<br /><a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/8.4/interactive/functions-window.html">http://www.postgresql.org/docs/8.4/interactive/functions-window.html</a><br
/><br/> Example:<br /> select username, row_number() over() from my_user_table;<br /> username | row_number <br />
-------------+------------<br/> admin | 1<br /> everyone | 2<br /><br /><pre
class="moz-signature"cols="72">--
Andreas Joseph Krogh <a class="moz-txt-link-rfc2396E"
href="mailto:andreak@officenet.no"><andreak@officenet.no></a>
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 | |
------------------------+---------------------------------------------+
</pre>
<div class="Section1"><p class="MsoNormal"><span lang="EN-US" style="font-size:12.0pt;font-family:"Times New Roman","serif""><br/> > I don't understand what you mean by missing ROWNUM feature, PG got this with windows-functionsin 8.4:<br /> > </span><span style="font-size:12.0pt;font-family:"Times New Roman","serif""><a href="http://www.postgresql.org/docs/8.4/interactive/functions-window.html"><span lang="EN-US">http://www.postgresql.org/docs/8.4/interactive/functions-window.html</span></a></span><spanlang="EN-US" style="font-size:12.0pt;font-family:"TimesNew Roman","serif""><br /><br /></span><span lang="EN-US" style="color:#1F497D"></span><pclass="MsoNormal"><span lang="EN-US" style="color:#1F497D"> </span><p class="MsoNormal"><spanlang="EN-US" style="color:#1F497D">I'm unfortunately still using 8.3.</span><p class="MsoNormal"><spanlang="EN-US" style="color:#1F497D"> </span><p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">sorrynot to have mentioned that.</span><p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"> </span><pclass="MsoNormal"><span lang="EN-US" style="color:#1F497D">Marc Mamin</span><p class="MsoNormal"><spanlang="EN-US" style="color:#1F497D"> </span></div>