Generate Up Down Series

I AM THE GREAT EVAN "THE GREAT" CARROLL

Note this module uses SFRM_Materialize_Preferred. It will outperform generate_series when materialization is required.

Synopsis

The script inside the repo install_and_test demonstrates the Setup and Benchmarking entirely

This extension can be used with

SELECT *
FROM generate_up_down_series_evan(n,m);

Like this

SELECT generate_up_down_series_evan(3,2);
 generate_up_down_series
-------------------------
                       1
                       2
                       3
                       3
                       2
                       1
                       1
                       2
                       3
                       3
                       2
                       1

Setup

First you'll need to compile. Note on Debian you'll need postgresql-server-dev-all and build-essentials. Then you can install it with

$ make
$ sudo make install

Then to add or remove the extension, use

CREATE EXTENSION generate_up_down_series;
DROP EXTENSION generate_up_down_series;

Background

This question was inspired by this post by joanolo on DBA.StackExchange. Four years after asked, user Vérace decided to benchmark all of these solutions, including mine. Though mine was the most charming, elegant, and bueatiful solution (using only SQL) it was not the most efficient.

This is an obscene amount of overengineering. Don't do this. However, it should be maximally fast. This is a port of Erwin's answer to C. just to win. Becuase that's what I DO.

Salt

• You can't do this (easily) with SQL Server (as it's not open source). It's basically just the devil's stateful abacus.
• Oracle will add this feature for US 2 BILLION DOLLARS.
• Go open source, or go home.
• I am the realest DBA, ever.
• MySQL is not real database.

Links

• Erwin's logic
• Lots of copy-pasta from the generate_series code in PostgreSQL
• PostgreSQL has the best docs ever