Given two tables with an undefined row count with a name and value, how would I display a pivoted
CROSS JOIN of a function over their values.
CREATE TEMP TABLE foo AS SELECT x::text AS name, x::int FROM generate_series(1,10) AS t(x); CREATE TEMP TABLE bar AS SELECT x::text AS name, x::int FROM generate_series(1,5) AS t(x);
For example, if that function were multiplication, how would I generate a (multiplication) table like the one below,
All of those
(arg1,arg2,result) rows can be generated with
SELECT foo.name AS arg1, bar.name AS arg2, foo.x*bar.x AS result FROM foo CROSS JOIN bar;
So this is only a question of presentation, I would like this to also work with a custom name -- a name that is not simply the argument
CASTed to text but set in the table,
CREATE TEMP TABLE foo AS SELECT chr(x+64) AS name, x::int FROM generate_series(1,10) AS t(x); CREATE TEMP TABLE bar AS SELECT chr(x+72) AS name, x::int FROM generate_series(1,5) AS t(x);
I think this would be easily do-able with a CROSSTAB capable of a dynamic return-type.
SELECT * FROM crosstab( ' SELECT foo.x AS arg1, bar.x AS arg2, foo.x*bar.x FROM foo CROSS JOIN bar ', 'SELECT DISTINCT name FROM bar' ) AS **MAGIC**
But, without the
**MAGIC**, I get
ERROR: a column definition list is required for functions returning "record" LINE 1: SELECT * FROM crosstab(
For reference, using the above examples with names this is something more like what
SELECT * FROM crosstab( ' SELECT foo.x AS arg1, bar.x AS arg2, foo.x*bar.x FROM foo CROSS JOIN bar ' ) AS t(row int, i int, j int, k int, l int, m int);
But, now we're back to making assumptions about the content and size of the
bar table in our example. So if,
- The tables are of undefined length,
- Then the cross-join represents a cube of undefined dimension (because of above),
- The catagory-names (cross-tab parlance) are in the table
What's the best we can do in PostgreSQL without a "column definition list" to generate that kind of presentation?