January 18, 2023 at 7:00 am
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?
January 18, 2023 at 1:19 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply