From: "Keith Worthington" <keithw@narrowpathinc.com>
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: pgsql-sql@postgresql.org
Reply-To: KeithW@narrowpathinc.com
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
Date: Fri, 20 May 2005 14:27:35 -0400
Message-Id: <20050520181514.M54654@narrowpathinc.com>
In-Reply-To: <1116349263.22291.129.camel@state.g2switchworks.com>
References: 
 <DCD4903594CCF34CAFE200BDF3448D8D5B291A@tshuscodenmbx01.ERF.THOMSON.COM>
	<1116349263.22291.129.camel@state.g2switchworks.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset=iso-8859-1

On Tue, 17 May 2005 12:01:03 -0500, Scott Marlowe wrote
> On Thu, 2005-05-12 at 14:07, Dennis.Jiang@thomson.com wrote:
> > Hi:
> > 
> > Oracle has a pseudo-column "ROWNUM" to return the sequence
> > number in which a row was returned when selected from a table.
> > The first row ROWNUM is 1, the second is 2, and so on.
> > 
> > Does Postgresql have a similar pseudo-column "ROWNUM" as
> > Oracle? If so, we can write the following query:
> > 
> > select * 
> > from (select RowNum, pg_catalog.pg_proc.* 
> >         from pg_catalog.pg_proc) inline_view
> > where RowNum between 100 and 200;
> 
> You can get a functional equivalent with a temporary sequence:
> 
> create temp sequence rownum;
> select *, nextval('rownum') as rownum from sometable;
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

Scott,

I realize that this thread went off in another direction however your
suggestion proved very helpful for a problem that I was trying to solve.  I
wanted the row number of a set returned by a function.  Here is a chopped
version of the function that I wrote.

CREATE OR REPLACE FUNCTION func_bom(integer, integer)
  RETURNS SETOF func_bom AS
$BODY$
   DECLARE
      v_number ALIAS FOR $1;
      v_line   ALIAS FOR $2;
      v_type varchar(8);
      r_row interface.func_so_line_bom%rowtype;
   BEGIN
      SELECT tbl_item.item_type INTO v_type
        FROM tbl_line_item
        JOIN tbl_item
          ON tbl_line_item.item_id = tbl_item.id
       WHERE tbl_line_item.number = v_number
         AND tbl_line_item.line = v_line;
      IF v_type = 'ASY' THEN
         CREATE TEMP SEQUENCE row_number
            INCREMENT BY 1
            START WITH 1;
         FOR r_row IN SELECT tbl_line_item.number,
                             tbl_line_item.line,
                             nextval('row_number') AS subline,
                             tbl_assembly.quantity AS bom_quantity,
                             tbl_assembly.component_id AS bom_item_id,
                             tbl_item.item_type AS bom_item_type,
                             tbl_item.description AS bom_item_description
                        FROM tbl_line_item
                        LEFT JOIN tbl_assembly
                          ON ( tbl_line_item.item_id::text =
                               tbl_assembly.id::text
                             )
                        JOIN tbl_item
                          ON ( tbl_assembly.component_id::text =
                               tbl_item.id::text
                             )
                       WHERE tbl_line_item.number = v_number
                         AND tbl_line_item.line = v_line
                       ORDER BY tbl_line_item.number,
                                tbl_line_item.line,
                                tbl_assembly.component_id
         LOOP
            RETURN NEXT r_row;
         END LOOP;
         DROP SEQUENCE row_number;
      ELSIF    v_item_type = 'THIS'
            OR v_item_type = 'THAT'
            OR v_item_type = 'OTHER' THEN
         FOR r_row IN SELECT
            [snip]
         LOOP
            RETURN NEXT r_row;
         END LOOP;
      END IF;
      RETURN;
   END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE STRICT;

Although I have no need to limit the output I tried it just for giggles and it
worked fine.

SELECT * FROM func_bom(12345, 1) WHERE subline between 4 AND 6;

Thanks!

Kind Regards,
Keith