Create View with pre defined number of rows

Question

I´ve got the following situation:

I have multiple identical formatted tables (table 1, 2, 3) with a variable amount of rows (1-100).

I want to create a View which adds the flexible amount of "table 1" rows within the first 100 rows of the view, the missing view rows to 100 rows should be filled up with the same pre-defined default row.

"Table 2" rows should then be inserted from the View row 101 and "Table 3" from 201 on ...

My first thought was to simply combine multiple UNION All queries, which would work for once, but the number of rows added after the first table (for example 90) would not be dynamic and exceed the first 100 rows of the view with a growing size of table 1.

Is it possible to "fill up" rows in a view until a specified row number?

Answer 1

Assuming the tables have the columns id, col1 and col2, try

(SELECT id, col1, col2
 FROM (SELECT id, col1, col2, FALSE AS o
       FROM table1
       LIMIT 100
       UNION ALL
       SELECT 42, 'dummy', 'dummy', TRUE
       FROM generate_series(1, 100)) t1
 ORDER BY o
 LIMIT 100)
UNION ALL
(SELECT id, col1, col2
 FROM (SELECT id, col1, col2, FALSE AS o
       FROM table2
       LIMIT 100
       UNION ALL
       SELECT 42, 'dummy', 'dummy', TRUE
       FROM generate_series(1, 100)) t2
 ORDER BY o
 LIMIT 100)
UNION ALL ...