I was thinking of proposing that we provide something just about like that as a standard function (written in C, not in plpgsql, so that it would be available whether or not you'd installed plpgsql). There are some places in the information_schema that desperately need it --- right now, the value of FUNC_MAX_ARGS is effectively hard-wired into some of the information_schema views, which means they are broken if one changes that #define. We could fix this if we had a function like the above and exported FUNC_MAX_ARGS as a read-only GUC variable.
The attached patch introduces a C function as discussed above. Looks like this:
regression=# select * from pg_generate(42,45);
pg_generate
-------------
42
43
44
45
(4 rows)It also makes use of the function to replace the hard-wired parts of the information_schema.
I have not yet made documentation changes, pending an answer to this and other questions: what should this function be called? I'm at a loss as to a good name -- the idea of the name pg_generate() was that the function acts as a non-persistent sequence generator, but I don't really like that name.
Any ideas, or other comments? For example, should pg_generate() allow a finish value < start and therefore count backward? Should there be a three argument version allowing a step size?
Thanks,
Joe
Index: src/backend/catalog/information_schema.sql
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/catalog/information_schema.sql,v
retrieving revision 1.21
diff -c -r1.21 information_schema.sql
*** src/backend/catalog/information_schema.sql 17 Dec 2003 22:11:30 -0000 1.21
--- src/backend/catalog/information_schema.sql 29 Jan 2004 00:38:48 -0000
***************
*** 399,415 ****
CREATE FUNCTION _pg_keypositions() RETURNS SETOF integer
LANGUAGE sql
IMMUTABLE
! AS 'select 1 union all select 2 union all select 3 union all
! select 4 union all select 5 union all select 6 union all
! select 7 union all select 8 union all select 9 union all
! select 10 union all select 11 union all select 12 union all
! select 13 union all select 14 union all select 15 union all
! select 16 union all select 17 union all select 18 union all
! select 19 union all select 20 union all select 21 union all
! select 22 union all select 23 union all select 24 union all
! select 25 union all select 26 union all select 27 union all
! select 28 union all select 29 union all select 30 union all
! select 31 union all select 32';
CREATE VIEW constraint_column_usage AS
SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
--- 399,407 ----
CREATE FUNCTION _pg_keypositions() RETURNS SETOF integer
LANGUAGE sql
IMMUTABLE
! AS 'select g.s
! from pg_generate(1,(select setting from pg_settings where name =
''max_index_keys'')::int)
! as g(s);';
CREATE VIEW constraint_column_usage AS
SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
Index: src/backend/utils/adt/int.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/int.c,v
retrieving revision 1.59
diff -c -r1.59 int.c
*** src/backend/utils/adt/int.c 1 Dec 2003 21:52:37 -0000 1.59
--- src/backend/utils/adt/int.c 29 Jan 2004 00:38:48 -0000
***************
*** 34,39 ****
--- 34,40 ----
#include <ctype.h>
#include <limits.h>
+ #include "funcapi.h"
#include "libpq/pqformat.h"
#include "utils/builtins.h"
***************
*** 1021,1023 ****
--- 1022,1088 ----
PG_RETURN_INT16(arg1 >> arg2);
}
+
+ /*
+ * non-persistent numeric sequence generator
+ */
+ Datum
+ generate_int(PG_FUNCTION_ARGS)
+ {
+ FuncCallContext *funcctx;
+ int32 *fctx;
+ int32 result;
+ MemoryContext oldcontext;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ int32 start = PG_GETARG_INT32(0);
+ int32 finish = PG_GETARG_INT32(1);
+
+ if (finish < start)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("finish is less than start")));
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function
+ * calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* total number of tuples to be returned */
+ funcctx->max_calls = finish - start + 1;
+
+ /* allocate memory for user context */
+ fctx = (int32 *) palloc(sizeof(int32));
+
+ /*
+ * Use fctx to keep track of upper and lower bounds from call to
+ * call. It will also be used to carry over the spare value we get
+ * from the Box-Muller algorithm so that we only actually
+ * calculate a new value every other call.
+ */
+ *fctx = start;
+ funcctx->user_fctx = fctx;
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ fctx = funcctx->user_fctx;
+ result = (*fctx)++;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ /* do when there is more left to send */
+ SRF_RETURN_NEXT(funcctx, Int32GetDatum(result));
+ else
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+ }
+
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.318
diff -c -r1.318 pg_proc.h
*** src/include/catalog/pg_proc.h 6 Jan 2004 23:55:19 -0000 1.318
--- src/include/catalog/pg_proc.h 29 Jan 2004 00:38:49 -0000
***************
*** 3424,3429 ****
--- 3424,3433 ----
DATA(insert OID = 2509 ( pg_get_expr PGNSP PGUID 12 f f t f s 3 25 "25
26 16" _null_ pg_get_expr_ext - _null_ ));
DESCR("deparse an encoded expression with pretty-print option");
+ /* non-persistent sequence generator */
+ DATA(insert OID = 1079 ( pg_generate PGNSP PGUID 12 f f t t v 2 23 "23
23" _null_ generate_int - _null_ ));
+ DESCR("non-persistent sequence generator");
+
/*
* Symbolic values for provolatile column: these indicate whether the result
Index: src/include/utils/builtins.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/utils/builtins.h,v
retrieving revision 1.233
diff -c -r1.233 builtins.h
*** src/include/utils/builtins.h 19 Jan 2004 19:04:40 -0000 1.233
--- src/include/utils/builtins.h 29 Jan 2004 00:38:49 -0000
***************
*** 175,180 ****
--- 175,181 ----
extern Datum int2not(PG_FUNCTION_ARGS);
extern Datum int2shl(PG_FUNCTION_ARGS);
extern Datum int2shr(PG_FUNCTION_ARGS);
+ extern Datum generate_int(PG_FUNCTION_ARGS);
/* name.c */
extern Datum namein(PG_FUNCTION_ARGS);
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
