Обсуждение: [pgadmin-hackers] Autoformatting
Hi Hackers,
We are looking into auto formatting SQL text in the SQL editor. However, some users have expressed that this can be done in different ways. Are there preferences in this community fo rhow autoformatting should be done?
--
Raffi Holzer
Product Manager
Pivotal Labs
Hi On Mon, Feb 13, 2017 at 9:35 PM, Raffi Holzer <rholzer@pivotal.io> wrote: > Hi Hackers, > We are looking into auto formatting SQL text in the SQL editor. However, > some users have expressed that this can be done in different ways. Are there > preferences in this community fo rhow autoformatting should be done? I think that really depends on the specific feature being discussed. For example, auto-indentation is very handy, and whilst it (and most formatting features) should be configurable, I suspect many people will leave it turned on. On the other hand, something like auto-upper-casing of keywords may be either automatic, or shortcut initiated, e.g. hit Ctrl+U to upper-case the word under the cursor. Perhaps you should start by listing the specific features? For reference, here's what pgAdmin 3 had: * The auto-indent feature will automatically indent text to the same depth as the previous line when you press return. * Block indent text by selecting two or more lines and pressing the Tab key. * Block outdent text that has been indented using the current tab settings using Shift+Tab. * Comment out SQL by selecting some text and pressing the Control+K. * Uncomment SQL by selecting some text starting with a comment and pressing Control+Shift+K. * Shift the selected text to upper case by pressing Control+U. * Shift the selected text to lower case by pressing Control+Shift+U. pgAdmin 4 currently has: * Auto-indent (not currently possible to disable it) I'd definitely like to see the rest (and anything else that seems useful) added, and made configurable where appropriate. I suspect the only ones that should be truly automatic are auto-indent and and upper-casing of keywords. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Hi Dave,
When we were talking about auto formatting we were actually referring to a single button press doing all of these things to a large block of text. The use case here would be if you receive a large illegible query and paste it into the query editor it would either automatically auto-format or you would press a button and it would auto-format. We are trying to determine a few things. One, are there preferred formatting guidelines and two, how should this autoformatting be implemented? On a button press? Upon pasting in the text?
On Tue, Feb 14, 2017 at 5:53 AM, Dave Page <dpage@pgadmin.org> wrote:
Hi
On Mon, Feb 13, 2017 at 9:35 PM, Raffi Holzer <rholzer@pivotal.io> wrote:
> Hi Hackers,
> We are looking into auto formatting SQL text in the SQL editor. However,
> some users have expressed that this can be done in different ways. Are there
> preferences in this community fo rhow autoformatting should be done?
I think that really depends on the specific feature being discussed.
For example, auto-indentation is very handy, and whilst it (and most
formatting features) should be configurable, I suspect many people
will leave it turned on. On the other hand, something like
auto-upper-casing of keywords may be either automatic, or shortcut
initiated, e.g. hit Ctrl+U to upper-case the word under the cursor.
Perhaps you should start by listing the specific features? For
reference, here's what pgAdmin 3 had:
* The auto-indent feature will automatically indent text to the same
depth as the previous line when you press return.
* Block indent text by selecting two or more lines and pressing the Tab key.
* Block outdent text that has been indented using the current tab
settings using Shift+Tab.
* Comment out SQL by selecting some text and pressing the Control+K.
* Uncomment SQL by selecting some text starting with a comment and
pressing Control+Shift+K.
* Shift the selected text to upper case by pressing Control+U.
* Shift the selected text to lower case by pressing Control+Shift+U.
pgAdmin 4 currently has:
* Auto-indent (not currently possible to disable it)
I'd definitely like to see the rest (and anything else that seems
useful) added, and made configurable where appropriate. I suspect the
only ones that should be truly automatic are auto-indent and and
upper-casing of keywords.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Raffi Holzer
Product Manager
Pivotal Labs
Hi On Tue, Feb 14, 2017 at 3:02 PM, Raffi Holzer <rholzer@pivotal.io> wrote: > Hi Dave, > When we were talking about auto formatting we were actually referring to a > single button press doing all of these things to a large block of text. The > use case here would be if you receive a large illegible query and paste it > into the query editor it would either automatically auto-format or you would > press a button and it would auto-format. Oh, OK. > We are trying to determine a few > things. One, are there preferred formatting guidelines Hmm, it seems I missed SQL when I wrote our (basic) coding standards: https://www.pgadmin.org/docs4/1.x/coding_standards.html By default (in my opinion), we should have 4 character indents, new lines for logically distinct objects (e.g. columns or constraints on a table), commas etc. at the end of the line, keywords in upper case, e.g. SELECT a, b, FROM t ORDER BY a, b; However, that obviously becomes unwieldy in some cases, so common sense is needed: SELECT a, b, FROM t ORDER BY a, b; There should of course be a limit on the number of columns listed per line - but should the limit be a count or max width in chars? With a CREATE TABLE statement I think it's clear that each column should be on it's own line: CREATE TABLE t ( a serial NOT NULL PRIMARY KEY, b text ); In other words, we need to define (or adopt) a formal standard for this, and then write a parser/formatter - which seems like a decidedly non-trivial amount of work (FYI, I tried the Python format-sql module, and it failed on ~80% of my tests - probably either because it doesn't understand COPY or pl/pgsql). > and two, how should > this autoformatting be implemented? On a button press? Upon pasting in the > text? Definitely not on pasting - if you drop in a large script, it could take far too long, plus you may not care, or be looking at the original source in another tool and trying to mentally reconcile lines or statements to each other. I would say it should be a button/shortcut. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Thanks Dave! We came to the same conclusion about a button press. And thank you for the SQL guidelines.
On Wed, Feb 15, 2017 at 4:37 AM, Dave Page <dpage@pgadmin.org> wrote:
Hi
On Tue, Feb 14, 2017 at 3:02 PM, Raffi Holzer <rholzer@pivotal.io> wrote:
> Hi Dave,
> When we were talking about auto formatting we were actually referring to a
> single button press doing all of these things to a large block of text. The
> use case here would be if you receive a large illegible query and paste it
> into the query editor it would either automatically auto-format or you would
> press a button and it would auto-format.
Oh, OK.
> We are trying to determine a few
> things. One, are there preferred formatting guidelines
Hmm, it seems I missed SQL when I wrote our (basic) coding standards:
https://www.pgadmin.org/docs4/1.x/coding_standards.html
By default (in my opinion), we should have 4 character indents, new
lines for logically distinct objects (e.g. columns or constraints on a
table), commas etc. at the end of the line, keywords in upper case,
e.g.
SELECT
a,
b,
FROM
t
ORDER BY
a,
b;
However, that obviously becomes unwieldy in some cases, so common
sense is needed:
SELECT
a, b,
FROM
t
ORDER BY
a, b;
There should of course be a limit on the number of columns listed per
line - but should the limit be a count or max width in chars? With a
CREATE TABLE statement I think it's clear that each column should be
on it's own line:
CREATE TABLE t (
a serial NOT NULL PRIMARY KEY,
b text
);
In other words, we need to define (or adopt) a formal standard for
this, and then write a parser/formatter - which seems like a decidedly
non-trivial amount of work (FYI, I tried the Python format-sql module,
and it failed on ~80% of my tests - probably either because it doesn't
understand COPY or pl/pgsql).
> and two, how should
> this autoformatting be implemented? On a button press? Upon pasting in the
> text?
Definitely not on pasting - if you drop in a large script, it could
take far too long, plus you may not care, or be looking at the
original source in another tool and trying to mentally reconcile lines
or statements to each other. I would say it should be a
button/shortcut.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Raffi Holzer
Product Manager
Pivotal Labs
Hello!
![16- unformatted query.png](/media/maillist_attaches/pgadmin-hackers/2017/02/16/CAPG3WN6ib=dzdPgAB4xYeomgcSzcwVfEMkEXgPDTa89U3b1RnA@mail.gmail.com/16- unformatted query.png)
![17- query highlighted.png](/media/maillist_attaches/pgadmin-hackers/2017/02/16/CAPG3WN6ib=dzdPgAB4xYeomgcSzcwVfEMkEXgPDTa89U3b1RnA@mail.gmail.com/17- query highlighted.png)
![18- formatted query.png](/media/maillist_attaches/pgadmin-hackers/2017/02/16/CAPG3WN6ib=dzdPgAB4xYeomgcSzcwVfEMkEXgPDTa89U3b1RnA@mail.gmail.com/18- formatted query.png)
Attached are current designs for SQL autoformatting. This feature is not developed yet, only mockups of a potential solution so technical implementation is still being discussed. Style guide for autoformatting doesn't yet exist either, what's shown is manually formatted by another person.
When a user highlights a section of text, a menu option appears below with the option to autoformat text.
Not included in this current design:
- other options available when highlighting multiple sections of text
- compacting queries
We're currently testing this design with pgAdmin3 users. Looking to validate with a few more before developing the feature. Let us know your thoughts!
1. Query pasted into text editor
![16- unformatted query.png](/media/maillist_attaches/pgadmin-hackers/2017/02/16/CAPG3WN6ib=dzdPgAB4xYeomgcSzcwVfEMkEXgPDTa89U3b1RnA@mail.gmail.com/16- unformatted query.png)
2. Query highlighted and autoformat option appears
![17- query highlighted.png](/media/maillist_attaches/pgadmin-hackers/2017/02/16/CAPG3WN6ib=dzdPgAB4xYeomgcSzcwVfEMkEXgPDTa89U3b1RnA@mail.gmail.com/17- query highlighted.png)
3. Query formatted
![18- formatted query.png](/media/maillist_attaches/pgadmin-hackers/2017/02/16/CAPG3WN6ib=dzdPgAB4xYeomgcSzcwVfEMkEXgPDTa89U3b1RnA@mail.gmail.com/18- formatted query.png)
On Wed, Feb 15, 2017 at 9:40 AM Raffi Holzer <rholzer@pivotal.io> wrote:
Thanks Dave! We came to the same conclusion about a button press. And thank you for the SQL guidelines.On Wed, Feb 15, 2017 at 4:37 AM, Dave Page <dpage@pgadmin.org> wrote:Hi
On Tue, Feb 14, 2017 at 3:02 PM, Raffi Holzer <rholzer@pivotal.io> wrote:
> Hi Dave,
> When we were talking about auto formatting we were actually referring to a
> single button press doing all of these things to a large block of text. The
> use case here would be if you receive a large illegible query and paste it
> into the query editor it would either automatically auto-format or you would
> press a button and it would auto-format.
Oh, OK.
> We are trying to determine a few
> things. One, are there preferred formatting guidelines
Hmm, it seems I missed SQL when I wrote our (basic) coding standards:
https://www.pgadmin.org/docs4/1.x/coding_standards.html
By default (in my opinion), we should have 4 character indents, new
lines for logically distinct objects (e.g. columns or constraints on a
table), commas etc. at the end of the line, keywords in upper case,
e.g.
SELECT
a,
b,
FROM
t
ORDER BY
a,
b;
However, that obviously becomes unwieldy in some cases, so common
sense is needed:
SELECT
a, b,
FROM
t
ORDER BY
a, b;
There should of course be a limit on the number of columns listed per
line - but should the limit be a count or max width in chars? With a
CREATE TABLE statement I think it's clear that each column should be
on it's own line:
CREATE TABLE t (
a serial NOT NULL PRIMARY KEY,
b text
);
In other words, we need to define (or adopt) a formal standard for
this, and then write a parser/formatter - which seems like a decidedly
non-trivial amount of work (FYI, I tried the Python format-sql module,
and it failed on ~80% of my tests - probably either because it doesn't
understand COPY or pl/pgsql).
> and two, how should
> this autoformatting be implemented? On a button press? Upon pasting in the
> text?
Definitely not on pasting - if you drop in a large script, it could
take far too long, plus you may not care, or be looking at the
original source in another tool and trying to mentally reconcile lines
or statements to each other. I would say it should be a
button/shortcut.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company--Raffi HolzerProduct ManagerPivotal Labs
Вложения
Hi
--
On Thu, Feb 16, 2017 at 5:19 PM, Shirley Wang <swang@pivotal.io> wrote:
Hello!Attached are current designs for SQL autoformatting. This feature is not developed yet, only mockups of a potential solution so technical implementation is still being discussed. Style guide for autoformatting doesn't yet exist either, what's shown is manually formatted by another person.When a user highlights a section of text, a menu option appears below with the option to autoformat text.Not included in this current design:- other options available when highlighting multiple sections of text
Per our discussion yesterday, I think this is my biggest concern. Other examples of options when blocks of text are highlighted are block indent/outdent (we already have indent), and block commenting.
- compacting queriesWe're currently testing this design with pgAdmin3 users. Looking to validate with a few more before developing the feature. Let us know your thoughts!
I have a couple more:
- I think the popup control panel should be in a different colour to the highlight color. Maybe the light blue we use on the bootstrap panels.
- Please ensure the formatting code is a implemented in a way that allows easy re-use. I'd like to be able to use it on the SQL and mSQL panels for example, to ensure consistent formatting for display which can be very hard to achieve in the templates alone.
- I would suggest starting a mailing list thread with a rough draft of the intended formatting rules. We can refine them together, and include them in the docs as well as implemented in the code.
Thanks!
1. Query pasted into text editor2. Query highlighted and autoformat option appears3. Query formattedOn Wed, Feb 15, 2017 at 9:40 AM Raffi Holzer <rholzer@pivotal.io> wrote:Thanks Dave! We came to the same conclusion about a button press. And thank you for the SQL guidelines.On Wed, Feb 15, 2017 at 4:37 AM, Dave Page <dpage@pgadmin.org> wrote:Hi
On Tue, Feb 14, 2017 at 3:02 PM, Raffi Holzer <rholzer@pivotal.io> wrote:
> Hi Dave,
> When we were talking about auto formatting we were actually referring to a
> single button press doing all of these things to a large block of text. The
> use case here would be if you receive a large illegible query and paste it
> into the query editor it would either automatically auto-format or you would
> press a button and it would auto-format.
Oh, OK.
> We are trying to determine a few
> things. One, are there preferred formatting guidelines
Hmm, it seems I missed SQL when I wrote our (basic) coding standards:
https://www.pgadmin.org/docs4/1.x/coding_standards.html
By default (in my opinion), we should have 4 character indents, new
lines for logically distinct objects (e.g. columns or constraints on a
table), commas etc. at the end of the line, keywords in upper case,
e.g.
SELECT
a,
b,
FROM
t
ORDER BY
a,
b;
However, that obviously becomes unwieldy in some cases, so common
sense is needed:
SELECT
a, b,
FROM
t
ORDER BY
a, b;
There should of course be a limit on the number of columns listed per
line - but should the limit be a count or max width in chars? With a
CREATE TABLE statement I think it's clear that each column should be
on it's own line:
CREATE TABLE t (
a serial NOT NULL PRIMARY KEY,
b text
);
In other words, we need to define (or adopt) a formal standard for
this, and then write a parser/formatter - which seems like a decidedly
non-trivial amount of work (FYI, I tried the Python format-sql module,
and it failed on ~80% of my tests - probably either because it doesn't
understand COPY or pl/pgsql).
> and two, how should
> this autoformatting be implemented? On a button press? Upon pasting in the
> text?
Definitely not on pasting - if you drop in a large script, it could
take far too long, plus you may not care, or be looking at the
original source in another tool and trying to mentally reconcile lines
or statements to each other. I would say it should be a
button/shortcut.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company--Raffi HolzerProduct ManagerPivotal Labs
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Вложения
Hello!
![autoformating-bluepopup.png](/media/maillist_attaches/pgadmin-hackers/2017/05/04/CAPG3WN46pgzxKERPpr4TSpVFtyeMhParjvKAUzXWOuwZ4qJPhg@mail.gmail.com/autoformating-bluepopup.png)
![formatted query.png](/media/maillist_attaches/pgadmin-hackers/2017/05/04/CAPG3WN46pgzxKERPpr4TSpVFtyeMhParjvKAUzXWOuwZ4qJPhg@mail.gmail.com/formatted query.png)
We're starting development on this soon, so pulling this thread back up.
Here is an update on the design of the pop up, which matches the style we decided for alerts.
![autoformating-bluepopup.png](/media/maillist_attaches/pgadmin-hackers/2017/05/04/CAPG3WN46pgzxKERPpr4TSpVFtyeMhParjvKAUzXWOuwZ4qJPhg@mail.gmail.com/autoformating-bluepopup.png)
![formatted query.png](/media/maillist_attaches/pgadmin-hackers/2017/05/04/CAPG3WN46pgzxKERPpr4TSpVFtyeMhParjvKAUzXWOuwZ4qJPhg@mail.gmail.com/formatted query.png)
On Fri, Feb 17, 2017 at 4:32 AM Dave Page <dpage@pgadmin.org> wrote:
HiOn Thu, Feb 16, 2017 at 5:19 PM, Shirley Wang <swang@pivotal.io> wrote:Hello!Attached are current designs for SQL autoformatting. This feature is not developed yet, only mockups of a potential solution so technical implementation is still being discussed. Style guide for autoformatting doesn't yet exist either, what's shown is manually formatted by another person.When a user highlights a section of text, a menu option appears below with the option to autoformat text.Not included in this current design:- other options available when highlighting multiple sections of textPer our discussion yesterday, I think this is my biggest concern. Other examples of options when blocks of text are highlighted are block indent/outdent (we already have indent), and block commenting.
I think these can be added in the future, but for now, we should focus on this one and establish a precedent for shortcut pop ups. It will be easier for contributors to add other options with this styling later when they want to, plus we'll get some additional validation that this location/color/copy works.
- compacting queriesWe're currently testing this design with pgAdmin3 users. Looking to validate with a few more before developing the feature. Let us know your thoughts!I have a couple more:- I think the popup control panel should be in a different colour to the highlight color. Maybe the light blue we use on the bootstrap panels.- Please ensure the formatting code is a implemented in a way that allows easy re-use. I'd like to be able to use it on the SQL and mSQL panels for example, to ensure consistent formatting for display which can be very hard to achieve in the templates alone.- I would suggest starting a mailing list thread with a rough draft of the intended formatting rules. We can refine them together, and include them in the docs as well as implemented in the code.
Yes! We'll do that.
Вложения
On Thu, May 4, 2017 at 7:58 PM, Shirley Wang <swang@pivotal.io> wrote:
Hello!We're starting development on this soon, so pulling this thread back up.Here is an update on the design of the pop up, which matches the style we decided for alerts.
I like it!
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Вложения
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;
/* Keywords Upper, Stacked, commas before */
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, indent = 2 spaces, commas before */
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, indent = \t, commas before */
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;
On Fri, May 5, 2017 at 4:38 AM, Dave Page <dpage@pgadmin.org> wrote:
On Thu, May 4, 2017 at 7:58 PM, Shirley Wang <swang@pivotal.io> wrote:Hello!We're starting development on this soon, so pulling this thread back up.Here is an update on the design of the pop up, which matches the style we decided for alerts.I like it!--Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Вложения
On Mon, May 8, 2017 at 3:50 PM, Robert Eckhardt <reckhardt@pivotal.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.oidleft join pg_attribute att on dep.objid = att.attrelid and dep.objsubid = att.attnumwhere dep.objid = 16385 :: oid and classid in (select oid from pg_classwhere 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 ownertableFROM pg_depend depleft join pg_class clON dep.objid = cl.oidleft join pg_attribute attON dep.objid = att.attrelidAND dep.objsubid = att.attnumWHERE dep.objid = 16385 :: oidAND classid IN (SELECT oidFROM pg_classWHERE 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
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Tue, May 9, 2017 at 4:26 AM, Dave Page <dpage@pgadmin.org> wrote:
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 DISTINCTdep.deptype,dep.classid,coalesce(coc.relname, clrw.relname) AS ownertableFROMpg_depend depLEFT JOIN pg_class cl ON dep.objid = cl.oidLEFT JOIN pg_attribute att ON dep.objid = att.attrelid AND dep.objsubid = att.attnumWHEREdep.objid = 16385::oid ANDclassid IN (SELECToidFROMpg_classWHERErelname IN ('pg_class', 'pg_constraint'))ORDER BYclassid,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
Sorry I let this linger. Let's go with the style you currently have. With respect to your additional comments.
- Sub-Select should be a new line and indented as in your example.
- For multiple quals in the joins; the qual following the AND should be on the next line, and should it be indented one level
-- Rob
On Sun, May 28, 2017 at 11:54 AM, Robert Eckhardt <reckhardt@pivotal.io> wrote: > > > On Tue, May 9, 2017 at 4:26 AM, Dave Page <dpage@pgadmin.org> wrote: >> >> >> 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 > > > Sorry I let this linger. Let's go with the style you currently have. With > respect to your additional comments. To be clear, I wasn't insisting on that style - but it is (roughly) what we try to use and what I prefer. > > - Sub-Select should be a new line and indented as in your example. Sounds good. > - For multiple quals in the joins; the qual following the AND should be on > the next line, and should it be indented one level OK. Thanks! -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Hello,
Just curious to know if we are still working on this feature?
--
Regards,
Murtuza
On Mon, May 29, 2017 at 5:21 AM, Dave Page <dpage@pgadmin.org> wrote:
On Sun, May 28, 2017 at 11:54 AM, Robert Eckhardt <reckhardt@pivotal.io> wrote:
>
>
> On Tue, May 9, 2017 at 4:26 AM, Dave Page <dpage@pgadmin.org> wrote:
>>
>>
>> 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
>
>
> Sorry I let this linger. Let's go with the style you currently have. With
> respect to your additional comments.
To be clear, I wasn't insisting on that style - but it is (roughly)
what we try to use and what I prefer.
>
> - Sub-Select should be a new line and indented as in your example.
Sounds good.
> - For multiple quals in the joins; the qual following the AND should be on
> the next line, and should it be indented one level
OK.
Thanks!
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Hi Murtuza, We had to put the autoformatting feature on hold for a while to focus on some greenplum work. We have a library in progress but we're not currently far enough along with this to submit a patch. Thanks, ~Jing |
On Wed, Sep 13, 2017 1:13 AM, Murtuza Zabuawala murtuza.zabuawala@enterprisedb.com wrote:
Hello,Just curious to know if we are still working on this feature?--Regards,MurtuzaOn Mon, May 29, 2017 at 5:21 AM, Dave Page <dpage@pgadmin.org> wrote:On Sun, May 28, 2017 at 11:54 AM, Robert Eckhardt <reckhardt@pivotal.io> wrote:
>
>
> On Tue, May 9, 2017 at 4:26 AM, Dave Page <dpage@pgadmin.org> wrote:
>>
>>
>> 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
>
>
> Sorry I let this linger. Let's go with the style you currently have. With
> respect to your additional comments.
To be clear, I wasn't insisting on that style - but it is (roughly)
what we try to use and what I prefer.
>
> - Sub-Select should be a new line and indented as in your example.
Sounds good.
> - For multiple quals in the joins; the qual following the AND should be on
> the next line, and should it be indented one level
OK.
Thanks!
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers