Re: WIP: pg_pretty_query

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

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

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.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