Re: Does Postgresql have a similar pseudo-column "ROWNUM" as - Mailing list pgsql-sql

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