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 */
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to