Re: WIP: pg_pretty_query

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.

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;

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