> In article <[EMAIL PROTECTED]>, > Mike Mascari <[EMAIL PROTECTED]> writes: > >> [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match