Skip site navigation (1) Skip section navigation (2)

Re: Does Postgresql have a similar pseudo-column "ROWNUM" as

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
Date: 2005-05-20 18:27:35
Message-ID: 20050520181514.M54654@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-phppgsql-sql
On Tue, 17 May 2005 12:01:03 -0500, Scott Marlowe wrote
> On Thu, 2005-05-12 at 14:07, Dennis(dot)Jiang(at)thomson(dot)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

In response to

Responses

pgsql-php by date

Next:From: Scott MarloweDate: 2005-05-20 18:42:58
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
Previous:From: Sean DavisDate: 2005-05-18 20:20:26
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as

pgsql-sql by date

Next:From: John DeSoiDate: 2005-05-20 18:36:53
Subject: Re: How do I quit in the middle of a SQL script?
Previous:From: Andrew HammondDate: 2005-05-20 17:54:35
Subject: Re: Transaction in plpgslq

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group