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
