Re: Autoformatting

On Mon, May 8, 2017 at 3:50 PM, Robert Eckhardt <reckhardt(at)pivotal(dot)io>
wrote:

> Over the weekend I created some options for formatting. certainly this
> isn't complete but I hope it is a good starting point for the conversation.
> Copy and paste lost syntax highlighting but please assume that will be
> included.
>
> /* Unformatted */
>
> select distinct dep.deptype,dep.classid,coalesce(coc.relname,
> clrw.relname) as
> ownertable from pg_depend dep left join pg_class cl on dep.objid = cl.oid
> left join pg_attribute att on dep.objid = att.attrelid and dep.objsubid =
> att.attnum
> where dep.objid = 16385 :: oid and classid in (select oid from pg_class
> where relname in ( 'pg_class', 'pg_constraint' )) order by
> classid,cl.relkin;
>
>
> /* Keywords Upper, Stacked, commas after */
>
> SELECT DISTINCT dep.deptype,
> dep.classid,
> Coalesce(coc.relname, clrw.relname) AS ownertable
> FROM pg_depend dep
> left join pg_class cl
> ON dep.objid = cl.oid
> left join pg_attribute att
> ON dep.objid = att.attrelid
> AND dep.objsubid = att.attnum
> WHERE dep.objid = 16385 :: oid
> AND classid IN (SELECT oid
> FROM pg_class
> WHERE relname IN ( 'pg_class', 'pg_constraint' ))
> ORDER BY classid,
> cl.relkin;
>

Snipping the rest, as I think 'commas before' are the work of Chthulu.
Aside from the fact that they look ugly, the whole point of a comma is to
denote a pause/separation after a word.

Anyhoo, The standard we try to use in pgAdmin at the moment is slightly
different from the example you've given. To add it into the mix...

/* Keywords Upper, 4 space indent, commas after, AND/OR after,
* no spaces after ( or before ), or around ::
*/
SELECT DISTINCT
dep.deptype,
dep.classid,
coalesce(coc.relname, clrw.relname) AS ownertable
FROM
pg_depend dep
LEFT JOIN pg_class cl ON dep.objid = cl.oid
LEFT JOIN pg_attribute att ON dep.objid = att.attrelid AND dep.objsubid
= att.attnum
WHERE
dep.objid = 16385::oid AND
classid IN (
SELECT
oid
FROM
pg_class
WHERE
relname IN ('pg_class', 'pg_constraint')
)
ORDER BY
classid,
cl.relkind;

There are some subtleties that are open to personal taste there;

- Formatting of the sub-select - e.g. should the SELECT directly follow the
(, and should the rest be indented accordingly?

- Formatting of multiple quals in the joins; e.g. should the qual following
the AND be on the next line, and if so, should it be indented one level, or
to align with the qual above? Should the first qual be on the next line?

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company