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

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