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
>

Or a little different, with the over-loaded functions relying on the
original:

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


CREATE OR REPLACE FUNCTION public.enum(int4, int4) RETURNS  SETOF int4 AS '
  DECLARE
    numvals ALIAS FOR $1;
    minval ALIAS FOR $2;
    currval RECORD;
  BEGIN
    FOR currval IN SELECT minval + enum AS enum FROM enum(numvals) LOOP
      RETURN NEXT currval.enum;
    END LOOP;
    RETURN;
  END;
  '  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION public.enum(int4, int4, int4) RETURNS  SETOF
int4 AS ' DECLARE
    numvals ALIAS FOR $1;
    minval ALIAS FOR $2;
    maxval ALIAS FOR $3;
    currval RECORD;
/*
 From: Harald Fuchs
 Date: Wed, October 8, 2003 5:53
 To: pgsql-general@postgresql.org

 tuple_generator(integer)

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

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

  BEGIN
    FOR currval IN SELECT * FROM enum(numvals, minval) LOOP
      RETURN NEXT currval.enum % maxval;
    END LOOP;
    RETURN;
  END;
  '  LANGUAGE 'plpgsql' VOLATILE;


But, as interesting as these look, what would you actually use them for?


~Berend Tober