Re: [patch] A \pivot command for psql
От | Daniel Verite |
---|---|
Тема | Re: [patch] A \pivot command for psql |
Дата | |
Msg-id | e8c7f43a-06a8-44d8-958b-af8e2689e1f8@mm обсуждение исходный текст |
Ответ на | Re: [patch] A \pivot command for psql (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: [patch] A \pivot command for psql
|
Список | pgsql-hackers |
Tom Lane wrote: > Is there a way to implement pivoting as a set-returning function? Not with the same ease of use. We have crosstab functions in contrib/tablefunc already, but the killer problem with PIVOT is that truly dynamic columns are never reachable directly. If we could do this: SELECT * FROM crosstab('select a,b,c FROM tbl'); and the result came back pivoted, with a column for each distinct value of b, there will be no point in a client-side pivot. But postgres (or I suppose any SQL interpreter) won't execute this, for not knowing beforehand what structure "*" is going to have. So what is currently required from the user, with dynamic columns, is more like: 1st pass: identify the columnsSELECT DISTINCT a FROM tbl; 2nd pass: inject the columns, in a second embedded query and in a record definition, with careful quoting: select * from crosstab( 'SELECT a,b,c FROM tbl ORDER BY 1', ' VALUES (col1),(col2),(col3)...' -- or 'select distinct...'again ) AS ct(b type, "col1" type, "col2" type, "col3" type) Compared to this, \pivot limited to the psql interpreter is a no-brainer, we could just write instead: => select a,b,c FROM tbl; => \pivot This simplicity is the whole point. It's the result of doing the operation client-side, where the record structure can be pivoted without the target structure being formally declared. Some engines have a built-in PIVOT syntax (Oracle, SQL server). I have looked only at their documentation. Their pivot queries look nicer and are possibly more efficient than with SET functions, but AFAIK one still needs to programmatically inject the list of column values into them, when that list is not static. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
В списке pgsql-hackers по дате отправления: