Thread

1. workaround for missing ROWNUM feature with the help of GUC variables

   Marc Mamin <m.mamin@intershop.de> — 2010-08-04T10:35:58Z

   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;
   
      
   
      
   
    
   
    
   
    
   
   

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

   Andreas Joseph Krogh <andreak@officenet.no> — 2010-08-04T20:07:54Z

   On 08/04/2010 12:35 PM, Marc Mamin wrote:
   >
   > 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 |                                             |
   ------------------------+---------------------------------------------+
   
   

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

   Marc Mamin <m.mamin@intershop.de> — 2010-08-06T12:19:49Z

   > 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
   <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