Re: Generating a SQL Server population routine

In article <3F81B176(dot)3060701(at)mascari(dot)com>,
Mike Mascari <mascarm(at)mascari(dot)com> writes:

> Martin_Hurst(at)dom(dot)com wrote:
>> Has some one come up with a similar type script that could be used in a
>> Postgresql database?

>> The script below was created for a SQLServer database.
>> Thx,
>> -Martin

> I haven't. But I was wondering if a general purpose tuple-generating
> function, which would be trivial to implement, might be worthwhile in
> PostgreSQL or perhaps added to Joe Conway's tablefunc module.
> Something like:

> tuple_generator(integer)

> which returns a set of numbers whose elements are the integer values
> between 1 and the number supplied.

How about this?

CREATE OR REPLACE FUNCTION enum (INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION enum (INT, INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT minval + currval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION enum (INT, INT, INT) RETURNS SETOF INT AS '
DECLARE
numvals ALIAS FOR $1;
minval ALIAS FOR $2;
maxval ALIAS FOR $3;
BEGIN
FOR currval IN 0 .. numvals - 1 LOOP
RETURN NEXT currval % (maxval - minval + 1) + minval;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

Usage: SELECT * FROM enum (numvals [, minval [, maxval]])
Returns numvals consecutive numbers, beginning with 0 or minval
Wraps around to minval if maxval is reached