pg_generate_sequence and info_schema patch (Was: SELECT Question) - Mailing list pgsql-patches

Joe Conway wrote:
> Tom Lane wrote:
>> 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:

The attached incorporates the feedback received. Specifically there is
now an int8 version of the function, and I left it as a simple
start-to-finish sequence generator. Result looks like this:

regression=# select * from pg_generate_sequence(4, 8);
  pg_generate_sequence
----------------------
                     4
                     5
                     6
                     7
                     8
(5 rows)

regression=# select * from pg_generate_sequence(8, 4);
ERROR:  finish is less than start

regression=# select * from pg_generate_sequence(8000000000, 8000000004);
  pg_generate_sequence
----------------------
            8000000000
            8000000001
            8000000002
            8000000003
            8000000004
(5 rows)

regression=# select * from pg_generate_sequence(3,8000000000);
ERROR:  range of start to finish is too large
HINT:  start to finish range must be less than 4294967295


I'm still not sure the name is the best -- other ideas welcome. Also,
I'm not sure if it would be a good thing, or too confusing, to document
pg_generate_sequence() on the "Sequence Manipulation Functions" page in
the docs. Any opinions on that?

If there are no objections I'll commit in 24 hours or so. Barring better
ideas, I'll probably add pg_generate_sequence() to "Sequence
Manipulation Functions".

Thanks,

Joe

? src/bin/pg_id/.deps
? src/bin/pg_id/pg_id
? src/interfaces/ecpg/compatlib/libecpg_compat.so.1.0
? src/interfaces/ecpg/ecpglib/libecpg.so.4.0
? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.1.0
? src/interfaces/libpgtcl/libpgtcl.so.2.4
? src/interfaces/libpq/libpq.so.3.1
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    31 Jan 2004 23:06:22 -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_sequence(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    31 Jan 2004 23:06:22 -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,1083 ----

      PG_RETURN_INT16(arg1 >> arg2);
  }
+
+ /*
+  * non-persistent numeric sequence generator
+  */
+ Datum
+ pg_generate_sequence_int4(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 the return value from call to 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/backend/utils/adt/int8.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/int8.c,v
retrieving revision 1.50
diff -c -r1.50 int8.c
*** src/backend/utils/adt/int8.c    1 Dec 2003 21:52:37 -0000    1.50
--- src/backend/utils/adt/int8.c    31 Jan 2004 23:06:22 -0000
***************
*** 14,21 ****
--- 14,23 ----
  #include "postgres.h"

  #include <ctype.h>
+ #include <limits.h>
  #include <math.h>

+ #include "funcapi.h"
  #include "libpq/pqformat.h"
  #include "utils/int8.h"

***************
*** 935,938 ****
--- 937,1005 ----
      pfree(s);

      PG_RETURN_TEXT_P(result);
+ }
+
+ /*
+  * non-persistent numeric sequence generator
+  */
+ Datum
+ pg_generate_sequence_int8(PG_FUNCTION_ARGS)
+ {
+     FuncCallContext       *funcctx;
+     int64               *fctx;
+     int64                result;
+     MemoryContext        oldcontext;
+
+     /* stuff done only on the first call of the function */
+     if (SRF_IS_FIRSTCALL())
+     {
+         int64            start = PG_GETARG_INT64(0);
+         int64            finish = PG_GETARG_INT64(1);
+
+         if (finish < start)
+             ereport(ERROR,
+                     (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                      errmsg("finish is less than start")));
+
+         if ((finish - start + 1) > UINT_MAX)
+             ereport(ERROR,
+                     (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                      errmsg("range of start to finish is too large"),
+                      errhint("start to finish range must be less than %u",
+                                 UINT_MAX)));
+
+         /* 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 = (int64 *) palloc(sizeof(int64));
+
+         /* Use fctx to keep the return value from call to 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, Int64GetDatum(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    31 Jan 2004 23:06:22 -0000
***************
*** 3424,3429 ****
--- 3424,3435 ----
  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 = 1068 (  pg_generate_sequence PGNSP PGUID 12 f f t t v 2 23 "23 23" _null_ pg_generate_sequence_int4
-_null_ )); 
+ DESCR("non-persistent sequence generator");
+ DATA(insert OID = 1069 (  pg_generate_sequence PGNSP PGUID 12 f f t t v 2 20 "20 20" _null_ pg_generate_sequence_int8
-_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    31 Jan 2004 23:06:22 -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 pg_generate_sequence_int4(PG_FUNCTION_ARGS);

  /* name.c */
  extern Datum namein(PG_FUNCTION_ARGS);
Index: src/include/utils/int8.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/utils/int8.h,v
retrieving revision 1.40
diff -c -r1.40 int8.h
*** src/include/utils/int8.h    1 Dec 2003 21:52:38 -0000    1.40
--- src/include/utils/int8.h    31 Jan 2004 23:06:22 -0000
***************
*** 112,115 ****
--- 112,117 ----
  extern Datum int8_text(PG_FUNCTION_ARGS);
  extern Datum text_int8(PG_FUNCTION_ARGS);

+ extern Datum pg_generate_sequence_int8(PG_FUNCTION_ARGS);
+
  #endif   /* INT8_H */