How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

Question

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 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,

1. The tables are of undefined length,
2. Then the cross-join represents a cube of undefined dimension (because of above),
3. 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?

Answer 1

Simple case, static SQL

The non-dynamic solution with crosstab() for the simple case:

SELECT * FROM crosstab(
  'SELECT b.x, f.name, f.x * b.x AS prod
   FROM   foo f, bar b
   ORDER  BY 1, 2'
   ) AS ct (x int, "A" int, "B" int, "C" int, "D" int, "E" int
                 , "F" int, "G" int, "H" int, "I" int, "J" int);

I order resulting columns by foo.name, not foo.x. Both happen to be sorted in parallel, but that's just the simple setup. Pick the right sort order for your case. The actual value of the second column is irrelevant in this query (1-parameter form of crosstab()).

We don't even need crosstab() with 2 parameters because there are no missing values by definition. See:

• PostgreSQL Crosstab Query

(You fixed the crosstab query in the question by replacing foo with bar in a later edit. This also fixes the query, but keeps working with names from foo.)

Unknown return type, dynamic SQL

Column names and types cannot be dynamic. SQL demands to know number, names and types of resulting columns at call time. Either by explicit declaration or from information in the system catalogs (That's what happens with SELECT * FROM tbl: Postgres looks up the registered table definition.)

You want Postgres to derive resulting columns from data in a user table. Not going to happen.

One way or the other, you need two round trips to the server. Either you create a cursor and then walk through it. Or you create a temp table and then select from it. Or you register a type and use it in the call.

Or you simply generate the query in one step and execute it in the next:

SELECT $$SELECT * FROM crosstab(
  'SELECT b.x, f.name, f.x * b.x AS prod
   FROM   foo f, bar b
   ORDER  BY 1, 2'
   ) AS ct (x int, $$
 || string_agg(quote_ident(name), ' int, ' ORDER BY name) || ' int)'
FROM   foo;

This generates the query above, dynamically. Execute it in the next step.

I am using dollar-quotes ($$) to keep handling of nested quotes simple. See:

• Insert text with single quotes in PostgreSQL

quote_ident() is essential to escape otherwise illegal column names (and possibly defend against SQL injection).

Related:

• PostgreSQL convert columns to rows? Transpose?
• Dynamic alternative to pivot with CASE and GROUP BY

Answer 2

What's the best we can do in PostgreSQL without a "column definition list" to generate that kind of presentation?

If you frame this as a presentation problem, you might consider a post-query presentation feature.

Newer versions of psql (9.6) come with \crosstabview, showing a result in crosstab representation without SQL support (since SQL can't produce this directly , as mentioned in @Erwin's answer: SQL demands to know number, names and types of resulting columns at call time)

For instance, your first query gives:

SELECT foo.name AS arg1, bar.name AS arg2, foo.x*bar.x AS result
FROM foo
CROSS JOIN bar
\crosstabview

 arg1 | 1  | 2  | 3  | 4  | 5  
------+----+----+----+----+----
 1    |  1 |  2 |  3 |  4 |  5
 2    |  2 |  4 |  6 |  8 | 10
 3    |  3 |  6 |  9 | 12 | 15
 4    |  4 |  8 | 12 | 16 | 20
 5    |  5 | 10 | 15 | 20 | 25
 6    |  6 | 12 | 18 | 24 | 30
 7    |  7 | 14 | 21 | 28 | 35
 8    |  8 | 16 | 24 | 32 | 40
 9    |  9 | 18 | 27 | 36 | 45
 10   | 10 | 20 | 30 | 40 | 50
(10 rows)

The second example with ASCII column names gives:

SELECT foo.name AS arg1, bar.name AS arg2, foo.x*bar.x
    FROM foo
    CROSS JOIN bar
  \crosstabview

 arg1 | I  | J  | K  | L  | M  
------+----+----+----+----+----
 A    |  1 |  2 |  3 |  4 |  5
 B    |  2 |  4 |  6 |  8 | 10
 C    |  3 |  6 |  9 | 12 | 15
 D    |  4 |  8 | 12 | 16 | 20
 E    |  5 | 10 | 15 | 20 | 25
 F    |  6 | 12 | 18 | 24 | 30
 G    |  7 | 14 | 21 | 28 | 35
 H    |  8 | 16 | 24 | 32 | 40
 I    |  9 | 18 | 27 | 36 | 45
 J    | 10 | 20 | 30 | 40 | 50
(10 rows)

See psql manual and https://wiki.postgresql.org/wiki/Crosstabview for more.

Answer 3

This is not a definitive solution

This is my best approach till now. Still need to convert the final array into columns.

First I've got the Cartesian product of both tables:

select foo.name xname, bar.name yname, (foo.x * bar.x)::text as val,
       ((row_number() over ()) - 1) / (select count(*)::integer from foo) as row
 from bar
     cross join foo
 order by bar.name, foo.name

But, I've added a row number just to identify every row of the first table.

((row_number() over ()) - 1) / (select count(*)::integer from foo)

Then I've buit the result in this format:

[Row name] [Array of values]


select col_name, values
from
(
select '' as col_name, array_agg(name) as values from foo
UNION
select fy.name as col_name,
    (select array_agg(t.val) as values
    from  
        (select foo.name xname, bar.name yname, (foo.x * bar.x)::text as val,
              ((row_number() over ()) - 1) / (select count(*)::integer from foo) as row
        from bar
           cross join foo
        order by bar.name, foo.name) t
    where t.row = fy.row)
from
    (select name, (row_number() over(order by name)) - 1 as row from bar) fy
) a
order by col_name;

+---+---------------------+
|   |      ABCDEFGHIJ     |
+---+---------------------+
| I |     12345678910     |
+---+---------------------+
| J |   2468101214161820  |
+---+---------------------+
| K |  36912151821242730  |
+---+---------------------+
| L |  481216202428323640 |
+---+---------------------+
| M | 5101520253035404550 |
+---+---------------------+ 

Converting it to string delimited by comas:

select col_name, values
from
(
select '' as col_name, array_to_string(array_agg(name),',') as values from foo
UNION
select fy.name as col_name,
    (select array_to_string(array_agg(t.val),',') as values
    from  
        (select foo.name xname, bar.name yname, (foo.x * bar.x)::text as val,
              ((row_number() over ()) - 1) / (select count(*)::integer from foo) as row
        from bar
           cross join foo
        order by bar.name, foo.name) t
    where t.row = fy.row)
from
    (select name, (row_number() over(order by name)) - 1 as row from bar) fy
) a
order by col_name;


+---+------------------------------+
|   | A,B,C,D,E,F,G,H,I,J          |
+---+------------------------------+
| I | 1,2,3,4,5,6,7,8,9,10         |
+---+------------------------------+
| J | 2,4,6,8,10,12,14,16,18,20    |
+---+------------------------------+
| K | 3,6,9,12,15,18,21,24,27,30   |
+---+------------------------------+
| L | 4,8,12,16,20,24,28,32,36,40  |
+---+------------------------------+
| M | 5,10,15,20,25,30,35,40,45,50 |
+---+------------------------------+

(Just to try it later: http://rextester.com/NBCYXA2183)

Answer 4

As a side note, it seems like SQL:2016 will accommodate this with Polymorphic table functions (ISO/IEC TR 19075-7:2017)

I found the link What's New in SQL:2016 but the author doesn't expand on this much.