Re: [patch] A \pivot command for psql

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 columns
SELECT 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