Create the AS clause for Postgres crosstab dynamically.

  • This is a continuation of Dynamically creating columns for crosstab in PostgreSQL.

    To be clear, I cannot produce the AS clause if I have a dataset with a variable amount of keys. For example, a table with tests (testdate, pupil, outcome) that begins with the pupils (Tom, Dick, Harry)

    This would be the crosstab:

    select * from crosstab(
    'select testdate, pupil, result from tests)',
    'select distinct pupil from tests order by pupil')
    as ct
    "testdate" text,
    "Dick" text,
    "Harry" text,
    "Tom" text)

    Will the experiment fail as soon as Sally joins? With a phrase like:

    ERROR:  invalid return type
    DETAIL: Query-specified return tuple has 4 columns but crosstab returns 5.

    According to what I've read from here, PostgreSQL requires knowledge of the return types and structure during planning, therefore I can't just return a variable number of columns. Is that correct?

  • Unless you're simply building dynamic SQL on the fly, 100%, there's no way to do this in PostgreSQL. If you choose to do dynamic SQL, understand, depending on what and how you code, that is the principal vector for SQL Injection, and probably should be avoided.

    What is the root issue you're attempting to solve? You're working extremely hard to NOT use PostgreSQL the way it's built. Either your requirements are off, or you need a different database system.

    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply