18

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,

Common multiplication table of 1..12

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?

  • 1
    Would JSON results be a good approach? Would an ARRAY be a good aprpoach? This way, the definition of the "output table" would already be known (and fixed). You put the flexibility within the JSON or the ARRAY. I guess it will depend a lot of the tools used afterwards to process the information. – joanolo Dec 27 '16 at 19:59
  • I would prefer it to be just like the above, if possible. – Evan Carroll Dec 27 '16 at 20:02
12

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:

(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:

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

Related:

|improve this answer|||||
  • I noticed that executing the query that you called "Unknown return type, dynamic SQL" actually just returns a string that represents another query, and then you say "execute it in the next step". Does this mean that it would be difficult for example to create a materialized view off of this? – Colin D Feb 19 '17 at 21:41
  • @ColinD: Not difficult, but plain impossible. You can create a MV from the generated SQL with known return type. But you can't have a MV with unknown return type. – Erwin Brandstetter Feb 20 '17 at 7:13
11

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.

|improve this answer|||||
1

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)

|improve this answer|||||
0

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.