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

Tom Lane wrote:
> folklore has it that Mariner II was lost to exactly such a bug).

Ouch -- got the point.

> If you want to allow the 3-parameter form to specify a negative step
> size, that's fine.  But don't use a heuristic to guess the intended
> step direction.

The attached patch implements the semantics you're looking for (I
think). Also attached is my test case output.

The one corner case not discussed is a step size of zero. Currently it
returns zero rows, but I considered having it generate an ERROR.

OK to commit?

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    2 Feb 2004 05:51:20 -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    2 Feb 2004 05:51:20 -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,1108 ----

      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);
+
+         /* 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;
+         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    2 Feb 2004 05:51:20 -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,1023 ----

      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);
+
+         /* 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;
+         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    2 Feb 2004 05:51:21 -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    2 Feb 2004 05:51:21 -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    2 Feb 2004 05:51:21 -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 */
-- one row: 3
select * from generate_series(3,3);
 generate_series
-----------------
               3
(1 row)

-- one row: 3
select * from generate_series(3,3,1);
 generate_series
-----------------
               3
(1 row)

-- one row: 3
select * from generate_series(3,3,2);
 generate_series
-----------------
               3
(1 row)

-- one row: 3
select * from generate_series(3,3,-2);
 generate_series
-----------------
               3
(1 row)

-- three rows: 1,2,3
select * from generate_series(1,3);
 generate_series
-----------------
               1
               2
               3
(3 rows)

-- three rows: 1,2,3
select * from generate_series(1,3,1);
 generate_series
-----------------
               1
               2
               3
(3 rows)

-- two rows: 1,3
select * from generate_series(1,3,2);
 generate_series
-----------------
               1
               3
(2 rows)

-- zero rows
select * from generate_series(1,3,-1);
 generate_series
-----------------
(0 rows)

-- zero rows
select * from generate_series(3,1);
 generate_series
-----------------
(0 rows)

-- zero rows
select * from generate_series(3,1,1);
 generate_series
-----------------
(0 rows)

-- zero rows
select * from generate_series(1,3,0);
 generate_series
-----------------
(0 rows)

-- three rows: 3,2,1
select * from generate_series(3,1,-1);
 generate_series
-----------------
               3
               2
               1
(3 rows)

-- two rows: 3,1
select * from generate_series(3,1,-2);
 generate_series
-----------------
               3
               1
(2 rows)

-- three rows: -3,-2,-1
select * from generate_series(-3,-1,1);
 generate_series
-----------------
              -3
              -2
              -1
(3 rows)

-- three rows: 80000000001,80000000002,80000000003
select * from generate_series(80000000001,80000000003);
 generate_series
-----------------
     80000000001
     80000000002
     80000000003
(3 rows)

-- zero rows
select * from generate_series(80000000001,80000000003,0);
 generate_series
-----------------
(0 rows)

-- three rows: 80000000001,80000000002,80000000003
select * from generate_series(80000000001,80000000003,1);
 generate_series
-----------------
     80000000001
     80000000002
     80000000003
(3 rows)

-- two rows: 80000000001,80000000003
select * from generate_series(80000000001,80000000003,2);
 generate_series
-----------------
     80000000001
     80000000003
(2 rows)

-- zero rows
select * from generate_series(80000000001,80000000003,-1);
 generate_series
-----------------
(0 rows)

-- zero rows
select * from generate_series(80000000003,80000000001);
 generate_series
-----------------
(0 rows)

-- zero rows
select * from generate_series(80000000003,80000000001,1);
 generate_series
-----------------
(0 rows)

-- three rows: 80000000003,80000000002,80000000001
select * from generate_series(80000000003,80000000001,-1);
 generate_series
-----------------
     80000000003
     80000000002
     80000000001
(3 rows)

-- two rows: 80000000003,80000000001
select * from generate_series(80000000003,80000000001,-2);
 generate_series
-----------------
     80000000003
     80000000001
(2 rows)

-- three rows: -80000000003,-80000000002,-80000000001
select * from generate_series(-80000000003,-80000000001,1);
 generate_series
-----------------
    -80000000003
    -80000000002
    -80000000001
(3 rows)