Skip site navigation (1) Skip section navigation (2)

Re: pg_generate_sequence and info_schema patch (Was: SELECT

From: Joe Conway <mail(at)joeconway(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Kris Jurka <books(at)ejurka(dot)com>, Alex <alex(at)meerkatsoft(dot)com>,Lada 'Ray' Lostak <ray(at)unreal64(dot)net>, pgsql-patches(at)postgresql(dot)org
Subject: Re: pg_generate_sequence and info_schema patch (Was: SELECT
Date: 2004-02-01 20:54:18
Message-ID: 401D677A.5050902@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-patches
Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)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?



Attachment: current.75.diff
Description: text/plain (11.0 KB)

In response to

Responses

pgsql-patches by date

Next:From: Tom LaneDate: 2004-02-01 20:57:00
Subject: Re: Patch for psql startup clarity
Previous:From: Markus BertheauDate: 2004-02-01 20:22:42
Subject: Re: Patch for psql startup clarity

pgsql-general by date

Next:From: Tom LaneDate: 2004-02-01 21:12:41
Subject: Re: pg_generate_sequence and info_schema patch (Was: SELECT Question)
Previous:From: Merrall, GraemeDate: 2004-02-01 20:38:33
Subject: Re: Search across multiple sources

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group