Postgres Pro
Downloads
Home   >   mailing lists

Thread: sql formatter/beautifier

sql formatter/beautifier

From
"Merlin Moncure"
Date:
I am wondering if anybody can suggest a good sql formatting tool.  I
searched for the obvious things on google and can't find a good
solution. The requirements are:

* linux version strongly preffered
* pl/pgsql preffered, but not required
* must be able to be run from script (bash, etc)
* must be highly configurable

free/open source preferred, but not required

merlin

Re: sql formatter/beautifier

From
"Joris Dobbelsteen"
Date:
PostGreSQL (7.4 and onward) has such a thing build-in, but its not
particulary good (simple case works, but once it gets complex it makes a
mess out of it).

For example the postgresql formatted version
$CREATE OR REPLACE VIEW "Subquery" AS
$ SELECT t1.a, t2.b
$   FROM ( SELECT 1 AS a, 2 AS x) t1
$   JOIN ((( SELECT 1 AS b, 2 AS x
$UNION ALL
$         SELECT 1 AS b, 2 AS x)
$UNION
$         SELECT 1 AS b, 2 AS x)
$UNION ALL
$         SELECT 1 AS b, 2 AS x) t2 ON t1.x = t2.x
$  WHERE (t1.a IN ( SELECT 1
$UNION
$    SELECT 2));
(hopefully the mailer doesn't mess with the spacing)

I just found http://psti.equinoxbase.com/
There is a online demo and it works perfectly on this query:
$CREATE OR REPLACE VIEW "Subquery" AS
$SELECT t1.a, t2.b
$FROM ( SELECT 1 AS a, 2 AS x) t1
$JOIN ((( SELECT 1 AS b, 2 AS x
$         UNION ALL
$         SELECT 1 AS b, 2 AS x)
$       UNION
$       SELECT 1 AS b, 2 AS x)
$      UNION ALL
$      SELECT 1 AS b, 2 AS x) t2 ON t1.x = t2.x
$WHERE (t1.a IN ( SELECT 1
$                 UNION
$                 SELECT 2));

Probably such a thing should be integrated into postgresql...

- Joris

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Merlin Moncure
>Sent: vrijdag 2 februari 2007 16:38
>To: postgres general
>Subject: [GENERAL] sql formatter/beautifier
>
>I am wondering if anybody can suggest a good sql formatting
>tool.  I searched for the obvious things on google and can't
>find a good solution. The requirements are:
>
>* linux version strongly preffered
>* pl/pgsql preffered, but not required
>* must be able to be run from script (bash, etc)
>* must be highly configurable
>
>free/open source preferred, but not required
>
>merlin
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so
>that your
>       message can get through to the mailing list cleanly
>

Re: sql formatter/beautifier

From
Aaron Bingham
Date:
Joris Dobbelsteen wrote:
> PostGreSQL (7.4 and onward) has such a thing build-in, but its not
> particulary good (simple case works, but once it gets complex it makes a
> mess out of it).
Hi,

Cleaning out my pgsql-general mail, I ran across your post.  How do I
invoke PostgreSQL's built-in SQL beautifier?  I wasn't able to find a
reference to this feature in the documentation.

Thanks,

--
--------------------------------------------------------------------
Aaron Bingham
Senior Software Engineer
Cenix BioScience GmbH
--------------------------------------------------------------------

Re: sql formatter/beautifier

From
"Joris Dobbelsteen"
Date:
If you use pgAdmin3 for example, it will format the definition in the
database for you. This will exclude stored procedures, which are stored
as-is.

The formatter isn't too good however...

There are better products on the market that will do the formatting
significantly better.
This one was nice: http://psti.equinoxbase.com/cgi-bin/handler.pl

- Joris

>-----Original Message-----
>From: Aaron Bingham [mailto:bingham@cenix-bioscience.com]
>Sent: donderdag 8 maart 2007 11:36
>To: Joris Dobbelsteen
>Cc: Merlin Moncure; postgres general
>Subject: Re: [GENERAL] sql formatter/beautifier
>
>Joris Dobbelsteen wrote:
>> PostGreSQL (7.4 and onward) has such a thing build-in, but its not
>> particulary good (simple case works, but once it gets
>complex it makes
>> a mess out of it).
>Hi,
>
>Cleaning out my pgsql-general mail, I ran across your post.
>How do I invoke PostgreSQL's built-in SQL beautifier?  I
>wasn't able to find a reference to this feature in the documentation.
>
>Thanks,
>
>--
>--------------------------------------------------------------------
>Aaron Bingham
>Senior Software Engineer
>Cenix BioScience GmbH
>--------------------------------------------------------------------