generate_series() games

generate_series() function is one of the most used functions in MicroOLAP Database Designer’s for PostgreSQL Reverse Engineering. But pre-8.x PostgreSQL releases do not have such function. And we can’t create any analogue on the server. So is there a way to emulate generate_series() behaviour? Yes, there is.

We’re using such trick:

         select i*10 + j as n
         from (select 0 union all 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) s1(i),
              (select 0 union all 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) s2(j)
         where (i*10 + j >= %d) AND (i*10 + j <= %d)

Replace first %d with lower boundary and the second %d with upper. Let’s see how the live SQL may look like with this trick:

SELECT
	pc.oid,
	pc.relname,
	ARRAY(SELECT pg_get_indexdef(pc.oid, g.n, True) FROM
	(select i*10 + j
         from (select 0 union all 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) s1(i),
              (select 0 union all 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) s2(j)
         where (i*10 + j >= 1) AND (i*10 + j <= pc.relnatts::int4))
               as g(n) ) as ind_attrs
FROM pg_class pc
WHERE pc.relkind = 'i'

Here we are fetching parameters for all indices.