Re: Generating a SQL Server population routine

In article <3F81B176.3060701@mascari.com>,
Mike Mascari <mascarm@mascari.com> writes:

> Martin_Hurst@dom.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