Postgresql Crosstab with Unknown Result

Question

I'm trying create a Crosstab query to retrieve custom values from my database, and match them to a parent entity. I have the crosstab functioning, but the problem I have is that my results are entirely unknown.

To explain, my application features a Custom Field module, where users of the application can define their own fields. The values for these fields are then entered by the user. Of course, the custom fields have their own names that are defined by the users, and can be changed by those users on the fly. There's also no upper limit to the number of fields that they could create. As such, there's no way for me to have the final_result of the crosstab defined. It needs to be dynamic and created at the moment of the query, and I'm not sure how to accomplish this.

Here's my current crosstab query:

SELECT *
FROM CROSSTAB(
    $$
    SELECT A.AGREEMENTNUMBER, CFCM.NAME, CFV.VALUE
    FROM AGREEMENTS A
        LEFT JOIN CUSTOMFIELDVALUES CFV ON CFV.FK_AGREEMENT = A.ID
        LEFT JOIN CUSTOMFIELDCONTRACTMETA CFCM ON CFCM.ID = CFV.FK_CUSTOMFIELDMETA
    ORDER BY 1,2
    $$,
    $$
    SELECT CFCM.NAME
    FROM CUSTOMFIELDCONTRACTMETA CFCM
    $$
) AS final_result(AGREEMENTNUMBER TEXT, TEST_TEXT TEXT, TEST_CURRENCY TEXT, TEST_LIST TEXT);

You can see that I currently have three custom fields prepared in my application (TEST_TEXT, TEST_CURRENCY, and TEST_LIST). The problem is that users would likely enter things like "Amount of money spent this quarter" or something like that. And the biggest problem is that these are entirely controlled by the users and I have no ability to predict what they will have. Is there any possible way to define my result of the crosstab utilizing a query? I'd essentially like to use the same query that I utilized for the Category_SQL. So, something akin to:

SELECT *
FROM CROSSTAB(
    $$
    SELECT A.AGREEMENTNUMBER, CFCM.NAME, CFV.VALUE
    FROM AGREEMENTS A
        LEFT JOIN CUSTOMFIELDVALUES CFV ON CFV.FK_AGREEMENT = A.ID
        LEFT JOIN CUSTOMFIELDCONTRACTMETA CFCM ON CFCM.ID = CFV.FK_CUSTOMFIELDMETA
    ORDER BY 1,2
    $$,
    $$
    SELECT CFCM.NAME
    FROM CUSTOMFIELDCONTRACTMETA CFCM
    $$
) AS final_result(AGREEMENTNUMBER TEXT, SELECT CFCM.NAME FROM CUSTOMFIELDCONTRACTMETA CFCM);

Answer 1

In case someone else has a similar problem, I've come up with a solution that works for me. It requires some additional steps, but nothing too bad.

Handling dynamic results can be done by utilizing a TYPE, that is dropped and redefined whenever a change is made to the custom fields. A function that returns a SETOF this type is also recreated with the type, and then used in the query that will be my VIEW.

CREATE OR REPLACE FUNCTION CROSSTAB_TYPE_BUILD() RETURNS VOID AS $outer$
DECLARE
    crossTabResultDef           VARCHAR;
    customFieldMeta             RECORD;
BEGIN
    DROP TYPE IF EXISTS CUSTOMFIELDCROSSTABTYPE CASCADE;
    
    crossTabResultDef := 'AGREEMENT_ID NUMERIC';
    
    FOR customFieldMeta IN
        SELECT CFCM.NAME
        FROM CUSTOMFIELDCONTRACTMETA CFCM
            INNER JOIN CUSTOMFIELDMETA CFM ON CFCM.ID = CFM.ID
        WHERE CFM.TYPE != 6
    LOOP
        crossTabResultDef := crossTabResultDef || ', "' || customFieldMeta.NAME || '" TEXT';
    END LOOP;
    
    EXECUTE 'CREATE TYPE CUSTOMFIELDCROSSTABTYPE AS (' || crossTabResultDef || ');';    
    
    CREATE OR REPLACE FUNCTION CROSSTAB_CUSTOMFIELDVALUES() RETURNS SETOF CUSTOMFIELDCROSSTABTYPE AS $inner$
    DECLARE
        crossTabResultDef           VARCHAR;
        customFieldMeta             RECORD;
    BEGIN
        crossTabResultDef := 'AGREEMENT_ID NUMERIC';

        FOR customFieldMeta IN
            SELECT CFCM.NAME
            FROM CUSTOMFIELDCONTRACTMETA CFCM
                INNER JOIN CUSTOMFIELDMETA CFM ON CFCM.ID = CFM.ID
            WHERE CFM.TYPE != 6
        LOOP
            crossTabResultDef := crossTabResultDef || ', "' || customFieldMeta.NAME || '" TEXT';
        END LOOP;

        RETURN QUERY EXECUTE 'SELECT *
            FROM CROSSTAB(
                ''SELECT A.ID AGREEMENT_ID, CFCM.NAME, CFV.VALUE
                FROM AGREEMENTS A
                    LEFT JOIN CUSTOMFIELDVALUES CFV ON CFV.FK_AGREEMENT = A.ID
                    LEFT JOIN CUSTOMFIELDCONTRACTMETA CFCM ON CFCM.ID = CFV.FK_CUSTOMFIELDMETA
                ORDER BY 1,2''
                ,
                ''SELECT CFCM.NAME
                FROM CUSTOMFIELDCONTRACTMETA CFCM''
            ) AS final_result(' || crossTabResultDef || ');';
    END;
    $inner$ LANGUAGE plpgsql;
END;
$outer$ LANGUAGE plpgsql;

The code is written for my own situation of course, but the basic functionality should be replicable for anyone else's situation. Essentially, we we have a function that builds the type needed for the result set, and this function also recreates another function to be utilized in select queries. So, the type is dropped if it exists. The ID of the primary entity is also assumed, and put into the crossTabResultDef variable. Next, the custom fields are looped through as a cursor, and each value is added to the crossTabResultDef variable. In my case, all values are stored as text, even if they are numeric or boolean - conversion happens inside of the application proper, but be sure to account for this if your own schema varies. The type is then recreated using this crossTabResultDef. Finally, the function for the select is recreated, using the rebuilt type as the return. This is necessary, since the type cannot be dropped without the cascade option, which means the second function is dropped. And near as I can tell, there is no CREATE OR REPLACE TYPE functionality, only CREATE TYPE, so the TYPE must be dropped to allow for redefinition. Note the "$outer$" and "$inner$" notations. This is needed to allow a function to be defined within the scope of a different function. Inside of that inner function, the same process of building the crossTabResultDef is performed so that it can be included in the crosstab query statement, which we can see is returning as an executed query.

Once everything has been created, the data needed for the crosstab is retrieved by using the inner function within a query.

SELECT A.*, CFV.*
FROM AGREEMENTS A
    LEFT JOIN CROSSTAB_CUSTOMFIELDVALUES() CFV ON CFV.AGREEMENT_ID = A.ID
;

This gives me exactly what I'm looking for, and allows the query to be utilized in spite of having no idea what kind of values to expect when it comes to my results. The only complication is that the "CROSSTAB_TYPE_BUILD" function needs to be run whenever changes are made to the custom fields, but I can include that as part of my standard PERSIST/MERGE/DELETE operations for the custom fields themselves.