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
>

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(at)postgresql(dot)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