It can be useful to have a source of natural numbers to join to e.g. to generate some kind of sequence.
I know two ways to create such a source:
create or replace view nats as select row_number() over (order by 1) as n from (select 1 union select 2) t1 join (select 1 union select 2) t2 join (select 1 union select 2) t3 join (select 1 union select 2) t4 join (select 1 union select 2) t5 ...
Append more lines as required. The t-number is the power-of-2 number of rows that the view produces e.g. t5 will produce 32 rows (2^5).
Using a function as a source of numbers seems to be slightly faster than the view, but more importantly, there's no limit on how many you can generate.
create or replace function nats(numeric) returns setof numeric as $$ -- return a table of ints from 0 to specified limit. -- usage: -- select nats(4096) as n; declare i numeric; begin for i in 0..$1 loop return next i; end loop; return; end; $$ language 'plpgsql' immutable strict;
I used the nats function to generate a sequence of date intervals like so:
select start_time , end_time , period_start_ts , period_end_ts from ( select CAST('1970-01-01' AS TIMESTAMP WITHOUT TIME ZONE) + CAST(CAST(period_start_ts AS VARCHAR) || ' s' AS INTERVAL) AS start_time , CAST('1970-01-01' AS TIMESTAMP WITHOUT TIME ZONE) + CAST(CAST(period_end_ts AS VARCHAR) || ' s' AS INTERVAL) AS end_time , period_start_ts , period_end_ts from ( select :start_ts + n * :period_len_secs as period_start_ts , :start_ts + (n + 1) * :period_len_secs as period_end_ts from (select nats(50000) as n) nats ) nats where period_end_ts <= :end_ts ) dates