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 CAST ed 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 tablefunc 's crosstab() wants.

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?