Thread: Re: [GENERAL] SELECT Question
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);
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 */
Joe Conway <mail@joeconway.com> 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.
> 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.)
> 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?
It is *not* a sequence function in the sense used on that page. I'm not
quite sure where to put it, but don't give people the impression that it
is related to sequence objects.
I was going to say "pg_generate_sequence" is a fine name, but after
thinking about the lack of relation to sequence objects I think we ought
not use "sequence" in the name. How about "pg_generate_series" or
"pg_generate_set"? Actually I think you could leave off the pg_ prefix
and just make it generate_series or generate_set. It's not
Postgres-specific in the same way that, say. pg_get_indexdef is.
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.
regards, tom lane
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> 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 */
Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> Joe Conway <mail@joeconway.com> 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)
And how do I get a zero-size set out of it? I think it's a really bad
idea to silently assume descending is meant if start > finish --- that
will create boundary-case bugs in many scenarios. A looping construct
that cannot iterate zero times is broken and dangerous (see Fortran DO
loops for context ... folklore has it that Mariner II was lost to
exactly such a bug).
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.
> 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?
Something odd there. The src/bin/pg_id subdirectory should be entirely
gone in recent checkouts, and I'm not sure why you're seeing .so files
still laying about but they shouldn't be there either ...
regards, tom lane
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)
Joe Conway <mail@joeconway.com> writes:
> 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.
I'd go for ERROR --- can't think of any reason to do otherwise, nor
any standard programming language that wouldn't consider that an error.
> OK to commit?
Don't forget to bump the catversion number.
regards, tom lane
Joe Conway <mail@joeconway.com> writes: > Tom Lane wrote: >> folklore has it that Mariner II was lost to exactly such a bug). > Ouch -- got the point. BTW, I think I was beating you over the head with an urban legend. Some idle googling revealed the true facts of the Mariner failure: http://www.rchrd.com/Misc-Texts/Famous_Fortran_Errors Nonetheless, it's well established that loops should be able to do nothing when doing nothing is called for. Even Fortran got the point after awhile... regards, tom lane
Tom Lane wrote: > BTW, I think I was beating you over the head with an urban legend. > Some idle googling revealed the true facts of the Mariner failure: > http://www.rchrd.com/Misc-Texts/Famous_Fortran_Errors Oh well, I've been beat over the head with worse things, at least metaphorically ;-). Interesting reading though. Joe
Tom Lane wrote: > 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. A first shot at documentation for generate_series() is available here in html form: http://www.joeconway.com/functions-srf.html Feedback welcome. Thanks, Joe
Joe Conway <mail@joeconway.com> writes: > A first shot at documentation for generate_series() is available here in > html form: > http://www.joeconway.com/functions-srf.html > Feedback welcome. This bit seems unnecessarily vague: Depending on the requested combination of start, stop, and step, it is possible to return zero rows. I think you can provide a precise specification without losing simplicity of explanation. Maybe something like When step is positive, zero rows are returned if start > stop. Conversely, when step is negative, zero rows are returned if start < stop. It is an error for step to be zero. ... and then carry on with the examples, which seem fine (although the one showing the error for step=0 might be thought redundant with the text). regards, tom lane
> I think you can provide a precise specification without losing > simplicity of explanation. Maybe something like > > When step is positive, zero rows are returned if start > stop. > Conversely, when step is negative, zero rows are returned if > start < stop. It is an error for step to be zero. Having something that generates a list of dates would be handy, however I guess you can do it with the current series generator by adding that many day intervals to a base date... Chris
Christopher Kings-Lynne wrote:
> Having something that generates a list of dates would be handy, however
> I guess you can do it with the current series generator by adding that
> many day intervals to a base date...
Seems to work:
regression=# select current_date + s.a as dates from
generate_series(1,3) as s(a);
dates
------------
2004-02-05
2004-02-06
2004-02-07
(3 rows)
Or even:
regression=# select current_date + s.a * '1 week'::interval as dates
from generate_series(1,3) as s(a);
dates
---------------------
2004-02-11 00:00:00
2004-02-18 00:00:00
2004-02-25 00:00:00
(3 rows)
Joe
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Joe Conway wrote: | Tom Lane wrote: | |> 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. | | | A first shot at documentation for generate_series() is available here in | html form: | http://www.joeconway.com/functions-srf.html I seen there: select * from generate_series(5,1,-2); ~ generate_series - ----------------- ~ 5 ~ 3 ~ 1 (3 rows) I understood on your past posts that instead this result was obtained with: select * from generate_series(5,1,2); ~ generate_series - ----------------- ~ 5 ~ 3 ~ 1 (3 rows) I think that is better have: ~ if start < end the series is ascending ~ if start > end the series is descending ~ if step > 0 the series is ascending ~ if step < 0 the series is descending ~ ( step can not be 0 ) if ( start < end ) and ( step < 0 ) the result set is empty if ( start > end ) and ( step > 0 ) the result set is empty IMHO this is the more natural behavior. my 2 cents. Regards Gaetano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAIYxo7UpzwH2SGd4RAnr9AJ4//4AO9VaIQmweneB0BJe8DvKJQgCfSJOF ejQN8TlGBjvntxoBJgk3uIk= =HcPJ -----END PGP SIGNATURE-----
Gaetano Mendola wrote: > select * from generate_series(5,1,-2); > I understood on your past posts that instead this result > was obtained with: > > select * from generate_series(5,1,2); > ~ generate_series > - ----------------- > ~ 5 > ~ 3 > ~ 1 > (3 rows) Tom objected to the original, so what you now see is what was agreed upon. > ~ ( step can not be 0 ) > > if ( start < end ) and ( step < 0 ) the result set is empty > if ( start > end ) and ( step > 0 ) the result set is empty Reread the thread. That was the conclusion and what the proposed documentation is at least trying to convey. As Tom pointed out earlier, I need to add a bit more detail to it. Joe
Clinging to sanity, chriskl@familyhealth.com.au (Christopher Kings-Lynne) mumbled into her beard:
>> I think you can provide a precise specification without losing
>> simplicity of explanation. Maybe something like
>> When step is positive, zero rows are returned if start > stop.
>> Conversely, when step is negative, zero rows are returned if
>> start < stop. It is an error for step to be zero.
>
> Having something that generates a list of dates would be handy,
> however I guess you can do it with the current series generator by
> adding that many day intervals to a base date...
Yes, that would be the way to do it.
Having generators (I'm not clear yet on whether it's more like Icon
generators or Common Lisp SERIES, or APL iota :-)) is a very useful
thing indeed.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://www3.sympatico.ca/cbbrowne/wp.html
"One often contradicts an opinion when what is uncongenial is really
the tone in which it was conveyed." -- Nietzsche