Re: WIP: pg_pretty_query

2012/8/7 Thom Brown <thom@linux.com>:
> On 7 August 2012 15:14, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> Hello
>>
>> last year we are spoke about reusing pretty print view code for some queries.
>>
>> Here is patch:
>>
>> this patch is really short - it is nice. But - it works only with
>> known database objects (probably we would it) and it doesn't format
>> subqueries well
>>
>>
>> postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
>> z  where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
>> z.a)', true, false);
>>                      pg_pretty_query
>> ----------------------------------------------------------
>>   SELECT x.a, z.a                                        +
>>     FROM foo, foo x, x z                                 +
>>    WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
>>             FROM foo                                     +
>>            WHERE foo.a = z.a))
>> (1 row)
>
> This looks odd:
>
> postgres=# SELECT pg_pretty_query('SELECT 1, (SELECT max(a.x) +
> greatest(2,3) FROM generate_series(4,10,2) a(x)) FROM
> generate_series(1,100) GROUP BY 1 ORDER BY 1, 2 USING < NULLS FIRST',
> true, false);
>                          pg_pretty_query
> ------------------------------------------------------------------
>   SELECT 1,                                                      +
>      ( SELECT max(a.x) + GREATEST(2, 3)                          +
>             FROM generate_series(4, 10, 2) a(x))                 +
>     FROM generate_series(1, 100) generate_series(generate_series)+
>    GROUP BY 1::integer                                           +
>    ORDER BY 1::integer, ( SELECT max(a.x) + GREATEST(2, 3)       +
>             FROM generate_series(4, 10, 2) a(x)) NULLS FIRST
> (1 row)
>
> USING < is removed completely (or if I used DESC, NULLS FIRST is then
> removed instead), "2" in the order by is expanded to its full query,
> and generate_series when used in FROM is repeated with its own name as
> a parameter.  I'm also not sure about the spacing before each line.
> SELECT, FROM and GROUP BY all appear out of alignment from one
> another.

it is issue - probably we can start deserialization just from parser
stage, not from rewriter stage - but then code will be significantly
longer and we cannot reuse current code for pretty print view.

>
> Plus it would be nice if we could support something like the following style:
>
> SELECT
>     field_one,
>     field_two + field_three
> FROM
>     my_table
> INNER JOIN
>     another_table
>     ON
>         my_table.field_one = another_table.another_field
>     AND
>         another_table.valid = true
> WHERE
>     field_one > 3
> AND
>     field_two < 10;
>

it is second issue - probably there are more lovely styles - CELKO,
your and other. I am not sure if we can support more styles in core
(contrib should be better maybe).

Regards

Pavel

> But that's just a nice-to-have.
> --
> Thom