Обсуждение: workaround for missing ROWNUM feature with the help of GUC variables

Поиск
Список
Период
Сортировка

workaround for missing ROWNUM feature with the help of GUC variables

От
"Marc Mamin"
Дата:
<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

От
Andreas Joseph Krogh
Дата:
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>

Re: workaround for missing ROWNUM feature with the help of GUC variables

От
"Marc Mamin"
Дата:
<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>