Tom Lane wrote:
Joe Conway <[EMAIL PROTECTED]> writes:
regression=# select * from pg_generate_sequence(8, 4);
ERROR:  finish is less than start

Hm, would it be better just to return an empty set? Certainly I'd expect pg_generate_sequence(1,0) to return an empty set with no error.

OK -- for this and other concerns below, I bit the bullet and decided to support descending series and step sizes other than one. Now it does this:


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

regression=# select * from generate_series(8, 4, 2);
 generate_series
-----------------
               8
               6
               4
(3 rows)

regression=# select * from generate_series(8000000000, 8000000004, 2);
 generate_series
-----------------
      8000000000
      8000000002
      8000000004
(3 rows)

regression=# select * from generate_series(8000000004, 8000000000, 3);
 generate_series
-----------------
      8000000004
      8000000001
(2 rows)

regression=# select * from generate_series(8000000004, 8000000000, -3);
ERROR:  step value must be greater than 0
HINT:  Use start greater than finish to create a descending series.

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

Is there a good reason for that restriction? (I've never thought it was good design for the SRF API to assume that the number of iterations could be determined in advance, anyway.)

See above -- fixed. But I'm not going to try to return > 4 billion values to illustrate ;-)


Actually I think you could leave off the pg_ prefix
and just make it generate_series or generate_set.

OK -- made it generate_series().


Maybe the best documentation answer is to create a new subsection in the
Functions chapter.  This may be our first standard set-returning
function but I bet it will not be the last, so the shortness of the
subsection doesn't bother me.

Agreed. I'll start this post-superbowl :-)


I'll apply in 24-48 hours if there are no further comments.

Thanks,

Joe

p.s. I did a `make distclean` prior to creating the attached diff. Do the lines at the top, e.g.:
? src/bin/pg_id/.deps
? src/bin/pg_id/pg_id
...
indicate stuff not being cleaned up when it ought to be?



? 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  1 Feb 2004 20:45:13 -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 generate_series(1,current_setting(''max_index_keys'')::int,1)
!         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 1 Feb 2004 20:45:13 -0000
***************
*** 34,39 ****
--- 34,40 ----
  #include <ctype.h>
  #include <limits.h>
  
+ #include "funcapi.h"
  #include "libpq/pqformat.h"
  #include "utils/builtins.h"
  
***************
*** 44,49 ****
--- 45,57 ----
  #define SHRT_MIN (-0x8000)
  #endif
  
+ typedef struct
+ {
+       int32           current;
+       int32           finish;
+       int32           step;
+ }     generate_series_fctx;
+ 
  /*****************************************************************************
   *     USER I/O ROUTINES                                                             
                                                  *
   *****************************************************************************/
***************
*** 1021,1023 ****
--- 1029,1121 ----
  
        PG_RETURN_INT16(arg1 >> arg2);
  }
+ 
+ /*
+  * non-persistent numeric series generator
+  */
+ Datum
+ generate_series_int4(PG_FUNCTION_ARGS)
+ {
+       return generate_series_step_int4(fcinfo);
+ }
+ 
+ Datum
+ generate_series_step_int4(PG_FUNCTION_ARGS)
+ {
+       FuncCallContext            *funcctx;
+       generate_series_fctx   *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);
+               int32                   step = 1;
+ 
+               /* see if we were given an explicit step size */
+               if (PG_NARGS() == 3)
+               {
+                       step = PG_GETARG_INT32(2);
+                       if (step < 1)
+                               ereport(ERROR,
+                                               
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                                                errmsg("step value must be greater 
than 0"),
+                                                errhint("Use start greater than 
finish to create"
+                                                                " a descending 
series.")));
+               }
+ 
+               /* 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);
+ 
+               /* allocate memory for user context */
+               fctx = (generate_series_fctx *) palloc(sizeof(generate_series_fctx));
+ 
+               /*
+                * Use fctx to keep state from call to call.
+                * Seed current with the original start value
+                */
+               fctx->current = start;
+               fctx->finish = finish;
+ 
+               /* use negative step if descending */
+               if (finish < start)
+                       fctx->step = -step;
+               else
+                       fctx->step = step;
+ 
+               funcctx->user_fctx = fctx;
+               MemoryContextSwitchTo(oldcontext);
+       }
+ 
+       /* stuff done on every call of the function */
+       funcctx = SRF_PERCALL_SETUP();
+ 
+       /*
+        * get the saved state and use current as the result for
+        * this iteration
+        */
+       fctx = funcctx->user_fctx;
+       result = fctx->current;
+ 
+       if ((fctx->step > 0 && fctx->current <= fctx->finish) ||
+               (fctx->step < 0 && fctx->current >= fctx->finish))
+       {
+               /* increment current in preparation for next iteration */
+               fctx->current += fctx->step;
+ 
+               /* 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        1 Feb 2004 20:45:13 -0000
***************
*** 14,27 ****
--- 14,35 ----
  #include "postgres.h"
  
  #include <ctype.h>
+ #include <limits.h>
  #include <math.h>
  
+ #include "funcapi.h"
  #include "libpq/pqformat.h"
  #include "utils/int8.h"
  
  
  #define MAXINT8LEN            25
  
+ typedef struct
+ {
+       int64           current;
+       int64           finish;
+       int64           step;
+ }     generate_series_fctx;
  
  /***********************************************************************
   **
***************
*** 936,938 ****
--- 944,1036 ----
  
        PG_RETURN_TEXT_P(result);
  }
+ 
+ /*
+  * non-persistent numeric series generator
+  */
+ Datum
+ generate_series_int8(PG_FUNCTION_ARGS)
+ {
+       return generate_series_step_int8(fcinfo);
+ }
+ 
+ Datum
+ generate_series_step_int8(PG_FUNCTION_ARGS)
+ {
+       FuncCallContext            *funcctx;
+       generate_series_fctx   *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);
+               int64                   step = 1;
+ 
+               /* see if we were given an explicit step size */
+               if (PG_NARGS() == 3)
+               {
+                       step = PG_GETARG_INT64(2);
+                       if (step < 1)
+                               ereport(ERROR,
+                                               
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                                                errmsg("step value must be greater 
than 0"),
+                                                errhint("Use start greater than 
finish to create"
+                                                                " a descending 
series.")));
+               }
+ 
+               /* 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);
+ 
+               /* allocate memory for user context */
+               fctx = (generate_series_fctx *) palloc(sizeof(generate_series_fctx));
+ 
+               /*
+                * Use fctx to keep state from call to call.
+                * Seed current with the original start value
+                */
+               fctx->current = start;
+               fctx->finish = finish;
+ 
+               /* use negative step if descending */
+               if (finish < start)
+                       fctx->step = -step;
+               else
+                       fctx->step = step;
+ 
+               funcctx->user_fctx = fctx;
+               MemoryContextSwitchTo(oldcontext);
+       }
+ 
+       /* stuff done on every call of the function */
+       funcctx = SRF_PERCALL_SETUP();
+ 
+       /*
+        * get the saved state and use current as the result for
+        * this iteration
+        */
+       fctx = funcctx->user_fctx;
+       result = fctx->current;
+ 
+       if ((fctx->step > 0 && fctx->current <= fctx->finish) ||
+               (fctx->step < 0 && fctx->current >= fctx->finish))
+       {
+               /* increment current in preparation for next iteration */
+               fctx->current += fctx->step;
+ 
+               /* 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       1 Feb 2004 20:45:14 -0000
***************
*** 3424,3429 ****
--- 3424,3440 ----
  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 series generator */
+ DATA(insert OID = 1066 (  generate_series PGNSP PGUID 12 f f t t v 3 23 "23 23 23" 
_null_ generate_series_step_int4 - _null_ ));
+ DESCR("non-persistent series generator");
+ DATA(insert OID = 1067 (  generate_series PGNSP PGUID 12 f f t t v 2 23 "23 23" 
_null_ generate_series_int4 - _null_ ));
+ DESCR("non-persistent series generator");
+ 
+ DATA(insert OID = 1068 (  generate_series PGNSP PGUID 12 f f t t v 3 20 "20 20 20" 
_null_ generate_series_step_int8 - _null_ ));
+ DESCR("non-persistent series generator");
+ DATA(insert OID = 1069 (  generate_series PGNSP PGUID 12 f f t t v 2 20 "20 20" 
_null_ generate_series_int8 - _null_ ));
+ DESCR("non-persistent series 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        1 Feb 2004 20:45:14 -0000
***************
*** 175,180 ****
--- 175,182 ----
  extern Datum int2not(PG_FUNCTION_ARGS);
  extern Datum int2shl(PG_FUNCTION_ARGS);
  extern Datum int2shr(PG_FUNCTION_ARGS);
+ extern Datum generate_series_int4(PG_FUNCTION_ARGS);
+ extern Datum generate_series_step_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    1 Feb 2004 20:45:14 -0000
***************
*** 112,115 ****
--- 112,118 ----
  extern Datum int8_text(PG_FUNCTION_ARGS);
  extern Datum text_int8(PG_FUNCTION_ARGS);
  
+ extern Datum generate_series_int8(PG_FUNCTION_ARGS);
+ extern Datum generate_series_step_int8(PG_FUNCTION_ARGS);
+ 
  #endif   /* INT8_H */
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to