0

I want to display a pivot table for the following data. I have a table with the 10000 records.

Table: Mat_test

CREATE TABLE Mat_test AS
SELECT DISTINCT 1 + floor(random() * 1500000)::integer AS id,
                2 + floor(random() * 1600000)::integer AS Numbers
FROM   generate_series(1, 10000) g;

Now creating function from which I want to display the crosstab table for the above records:

Function: Mat_test_cross_tab

CREATE OR REPLACE FUNCTION Mat_test_cross_tab (_retType anyelement
                                             , table_name varchar)
  RETURNS SETOF anyelement AS
$BODY$
DECLARE
    query varchar;
    cols varchar;
    cols1 varchar;
    cols2 varchar;
BEGIN
    cols := 'SELECT string_agg(numbers::varchar,'','') from '||table_name||'';
    execute cols into cols1;

    cols1 := 'SELECT REPLACE('''||cols1||''','','','' " INT,"'')';
    execute cols1 into cols2;

    cols2 := '"'||cols2||'" INT';
    raise info '%',cols2;

    query := 'SELECT * FROM Crosstab
         (
          '' SELECT id,numbers,count(*) as t
             from '||table_name||'
             group by 1,2
             order by 1,2'',
             $$select distinct numbers from '||table_name||'$$
          )
          AS (id integer,'||cols2')';

    RAISE INFO '%',query;

    RETURN QUERY EXECUTE query;

END;
$BODY$  LANGUAGE PLPGSQL;

Now I am creating function for creating the user defined type for returning the result set of the above function.

Function: Type_mat

CREATE OR REPLACE FUNCTION Type_mat(table_name varchar)
  RETURNS VOID AS
$BODY$
DECLARE 
    query varchar;
    cols varchar;
    cols1 varchar;
    cols2 varchar;
BEGIN
    cols := 'SELECT string_agg(numbers::varchar,'','') from '||table_name||'';
    execute cols into cols1;

    cols1 := 'SELECT REPLACE('''||cols1||''','','',''" INT,"'')';
    execute cols1 into cols2;
    cols2 := '"'||cols2||'" INT';

    query := 'CREATE TYPE Type_for_mat AS
         (
            id integer,
            '||cols2||'
          )';

    RAISE INFO '%',query;

    EXECUTE query;
END;
$BODY$  LANGUAGE PLPGSQL;

Call:

SELECT Type_mat('mat_test');

Getting an error:

ERROR: tables can have at most 1600 columns
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
MAK
  • 6,824
  • 25
  • 74
  • 131
  • 1
    The error message is pretty clear, isn't it? You cannot produce a table with more than 1600 columns. You have to rethink your approach. Arrays would be an unlimited alternative ... Also, this related answer may be of help: http://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by/15514334#15514334 – Erwin Brandstetter Mar 04 '15 at 17:58
  • @Erwin Brandstetter, How can Arrays would be helpful for this approach? – MAK Mar 05 '15 at 06:06
  • 1
    He's suggesting that you try to create an array with 1600+ elements instead of a row. – mu is too short Mar 05 '15 at 06:50
  • At the link I provided, there is a 2nd answer by Clodoaldo demonstrating a solution with array output: http://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by/15513319#15513319. Here is his more detailed, related answer: http://stackoverflow.com/questions/12988575/crosstab-with-a-large-or-undefined-number-of-categories/12989297#12989297 – Erwin Brandstetter Mar 05 '15 at 11:57
  • @Erwin Brandstetter, Yes! I checked that. But where I need to use the `array`. I mean in function `Type_mat` or in `Mat_test_cross_tab`? – MAK Mar 05 '15 at 12:04
  • @ErwinBrandstetter, The second function I have written because of creating `type` which is I will be using in first function. – MAK Mar 05 '15 at 12:06

0 Answers0