Re: WIP: pg_pretty_query

2012/8/7 Thom Brown <thom(at)linux(dot)com>:
> On 7 August 2012 15:14, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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