Re: [GENERAL] SELECT Question - Mailing list pgsql-patches

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:

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);