Re: WIP: pg_pretty_query

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.

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