Re: CROSSTAB( .. only one column has values... )

Hi

st 6. 1. 2021 v 21:47 odesílatel Daniel Verite <daniel(at)manitou-mail(dot)org>
napsal:

> Pavel Stehule wrote:
>
> > > *That* is a function of how Postgres set returning functions work, and
> not
> > > specific to crosstab(). It is not easily fixed. Patches to fix that
> would
> > > be
> > > welcomed!
> > >
> >
> >
> https://www.postgresql.org/message-id/flat/CAFj8pRC%2BhNzpH%2B0bPRCnqNncUCGjEvpwX%2B0nbhb1F7gwjYZZNg%40mail.gmail.com#9b3fbdd968c271668049a103bfc93880
>
> Oracle has Polymorphic Table Function but still it doesn't work
> seamlessly for dynamic pivots.
> This blog post illustrates why:
> https://blog.sqlora.com/en/dynamic-pivot-with-polymorphic-table-function/
> The "related posts" at the end are also interesting about this feature.
>
> Even before considering more dynamic SRFs in postgres, having a
> variable number of columns for a DML statement is problematic
> in general.
>
> When an SQL statement is parsed/prepared, it's not supposed to
> change its output structure, unless a DDL statement intervenes.
> The dynamic pivot must break this promise, since a simple INSERT or
> UPDATE or DELETE in the pivoting rows may cause the number of
> output columns to change across invocations of the same statement.
> That would mean that PREPARE would be basically unusable or
> unreliable for such statements.
>

yes

polymorphic functions need two steps. First step returns structure, second
data.

The prepared statements can be supported, but there should be a recheck if
the result has expected structure. And maybe in future, the prepared
statements can be more dynamic, and can be able to do replaning when it
will be necessary.

> I think the query parser is also not supposed to read data outside
> of the catalogs to determine the output structure of a query.
> This promise would also need to be broken by dynamic pivots
> implemented as a single-pass DML query.
>
> On the other hand, dynamic pivots can be done by generating the SQL
> dynamically and getting at the results in a second step, or returning a
> resultset embedded in a scalar (json).
> Speaking of features that don't exist but might someday, ISTM that
> CALL crosstab_dynamic(...) was more plausible than
> SELECT * FROM crosstab_dynamic(...), since CALL
> doesn't have the same contingencies as SELECT.
>
>
Yes, it is the Sybase way and it can be useful. But you cannot work with
the returned result more.

For users it isn't too important if they have to use polymorphic functions
or dynamic recordset or a PIVOT clause. Important is performance and
ergometry. Polymorphic functions can be used for more tasks than pivoting
(reading some external sources, ...). Dynamic recordsets or multi
recordsets can be nice features too. I like multi recordsets for reporting.
And Oracle's PIVOT clause is just handy and doesn't require programming.

Regards

Pavel

>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: https://www.manitou-mail.org
> Twitter: @DanielVerite
>