Re: WIP: pg_pretty_query

On Tue, Aug 07, 2012 at 04:54:12PM +0200, Pavel Stehule wrote:
> 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).

Would it be better to have output plugins and not privilege one?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate