Execute query string and get result with varying number of columns

Question

I am trying to transform a list of correlation values into a correlation matrix.
But I can't get the result after executing a string query.

The list/table(named corrtemp) is like this:

| name | colname | value |
| ---- | ------- | ----- |
| a    | a       | 1     |
| a    | b       | 0.5   |
| a    | c       | 0.3   |
| b    | a       | 0.5   |
| b    | b       | 1     |
| b    | c       | 0.2   |
| c    | a       | 0.3   |
| c    | b       | 0.2   |
| c    | c       | 1     |

The output should be like this:

| name |  a  |  b  |  c  |
| ---- |-----| --- | --- |
| a    |  1  | 0.5 | 0.3 |
| b    | 0.5 |  1  | 0.2 |
| c    | 0.3 | 0.2 |  1  |

The number of distinct names is unknown. So I loop to generate a string query. I want to execute it and get the query result in the result console. Currently, my code is like this:

do $$
declare 
    i integer := 0;
     sql0 varchar := 'SELECT DISTINCT temp0.name, cor0.value AS '||(SELECT DISTINCT name FROM corrtemp LIMIT 1)||' FROM corrtemp AS temp0'||chr(13);
     sql1 varchar := 'left JOIN corrtemp AS cor0 ON cor0.colname = temp0.name AND cor0.name = ''a'''||chr(13);
     sql2 varchar := 'WITH temp';
     sql4 varchar;
begin
    sql4 := sql0 || sql1 ||chr(13);
    i := i + 1;
    while i < (SELECT COUNT(DISTINCT name) FROM corrtemp) loop
        sql4 := chr(13)|| 
                sql2|| 
                CAST(i AS VARCHAR(5)) || 
                ' AS('||
                chr(13)||
                sql4||
                ')' ||
                'SELECT temp'|| CAST(i AS VARCHAR(5)) || '.*, cor'|| CAST(i AS VARCHAR(5)) ||'.value AS '||(SELECT DISTINCT name FROM corrtemp LIMIT 1 OFFSET i)||' FROM  temp'|| CAST(i AS VARCHAR(5))||chr(13)||
                'left JOIN corrtemp AS cor'|| CAST(i AS VARCHAR(5)) ||  ' ON cor'|| CAST(i AS VARCHAR(5)) || '.colname = temp'|| CAST(i AS VARCHAR(5)) || '.name AND cor'|| CAST(i AS VARCHAR(5)) || '.name = '''|| (SELECT DISTINCT name FROM corrtemp LIMIT 1 OFFSET i)||''''||chr(13);
        i := i + 1;
        end loop;
end$$

I tried EXECUTE sql4 inside the do and it only returns an 'OK' in the information console.

I need the query result to display in the result console just like after simply running SELECT * FROM corrtemp.

I am thinking using `CREATE OR REPLACE FUNCTION corrsql() RETURNS TABLE`, but I cannot decide the cols of the table. — sh12121212, Jan 29 '21 at 05:09
Like has been commented, SQL demands to know the return type at execution time. The result type cannot be completely dynamic. Maybe you know the ***maximum*** of possible distinct names and that's a reasonable number? — Erwin Brandstetter, Jan 30 '21 at 17:06
@ErwinBrandstetter Yes, I am able to know the number of the distinct names. The names and the number of names change should be allowed to change every time I call the function. — sh12121212, Feb 01 '21 at 02:32

sh12121212 · Answer 1 · 2021-01-30T08:17:11.303

I tried creating function. It doesn't work. Here's the code:

CREATE FUNCTION list2matrix() RETURNS setof record LANGUAGE plpgsql AS $$
declare 
   i integer := 0;
  sql0 varchar := 'SELECT DISTINCT temp0.name, cor0.value AS '||(SELECT DISTINCT name FROM corrtemp LIMIT 1)||' FROM corrtemp AS temp0'||chr(13);
  sql1 varchar := 'left JOIN corrtemp AS cor'|| CAST(i AS VARCHAR(5)) ||  ' ON cor'|| CAST(i AS VARCHAR(5)) || '.colname = temp'|| CAST(i AS VARCHAR(5)) || '.name AND cor'|| CAST(i AS VARCHAR(5)) || '.name = '''|| (SELECT DISTINCT name FROM corrtemp LIMIT 1 OFFSET i)||''''||chr(13);
  sql2 varchar := 'WITH temp';
  sql3 varchar := 'SELECT temp'|| CAST(i AS VARCHAR(5)) || '.*, cor'|| CAST(i AS VARCHAR(5)) ||'.value AS '||(SELECT DISTINCT name FROM corrtemp LIMIT 1 OFFSET i)||' FROM  temp'|| CAST(i AS VARCHAR(5))||chr(13) ;
  sql4 varchar;
begin
   sql4 := sql0 || sql1 ||chr(13);
  i := i + 1;
   while i < (SELECT COUNT(DISTINCT name) FROM corrtemp) loop
      sql4 := chr(13)|| 
            sql2|| 
        CAST(i AS VARCHAR(5)) || 
        ' AS('||
        chr(13)||
        sql4||
        ')' ||
        'SELECT temp'|| CAST(i AS VARCHAR(5)) || '.*, cor'|| CAST(i AS VARCHAR(5)) ||'.value AS '||(SELECT DISTINCT name FROM corrtemp LIMIT 1 OFFSET i)||' FROM  temp'|| CAST(i AS VARCHAR(5))||chr(13)||
        'left JOIN corrtemp AS cor'|| CAST(i AS VARCHAR(5)) ||  ' ON cor'|| CAST(i AS VARCHAR(5)) || '.colname = temp'|| CAST(i AS VARCHAR(5)) || '.name AND cor'|| CAST(i AS VARCHAR(5)) || '.name = '''|| (SELECT DISTINCT name FROM corrtemp LIMIT 1 OFFSET i)||''''||chr(13);
    i := i + 1;
     end loop;
         RETURN QUERY EXECUTE sql4;
end$$;

select list2matrix();

It sends the following error message to me:

ERROR: set-valued function called in context that cannot accept a set

It seems like I still have to define the table structure, which I really can't because the correlation matrix always changes.

score 0 · Answer 2 · answered Jan 29 '21 at 09:18

0

Unfortunately DO statement hasn't any input/output possibilities. You can change data inside DO statement, but you cannot to use DO statement to get any result.

answered Jan 29 '21 at 09:18

Pavel Stehule

42,331
5
91
94

Ｉtried create function returns sth (table/setof record). The problem I am facing right now is that I cannot decide the column name and column type. Both ways need this information to show the result table. – sh12121212 Jan 30 '21 at 07:07
You cannot to use functions for crosstabing too, because the structure of result must be known before execution. There is trick with cursors - http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html or if you use `psql` client, then you can use `\crosstabview` command. – Pavel Stehule Jan 30 '21 at 07:11
Here is the reality I am facing: 1, Due to technical limitations, I need a query that can directly output the correlation table. 2, I an unable to know the number of cols and the colnames, that's why I wrote a while loop to create the query string for whatever the correlation list I have. I read through your blog. It's stunning. But It still need to specify the col names of the output table, right? – sh12121212 Jan 30 '21 at 08:11
1

@sh12121212: there is no way you can run a SQL statement without making the number, name and data type of each column known to the server **before** the query is actually run. You could aggregate the "dynamic part" into a JSON column though if your frontend can handle that https://blog.sql-workbench.eu/post/pivot-alternatives/ – Jan 30 '21 at 08:32
@a_horse_with_no_name I tried json, and got stuck when the function, `json_populate_recordset` asked me to define the table structure. ;-) – sh12121212 Feb 03 '21 at 03:09

Execute query string and get result with varying number of columns

2 Answers2