SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
(postgresql) Fortmatting issues

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
(postgresql) Fortmatting issues
Author Message
michalk



Joined: 29 Aug 2014
Posts: 142

Post (postgresql) Fortmatting issues Reply with quote
Insights below are based on default settings of SA. Some of them can be fixed by changing formating templates. In case of some it's not possible (especially regarding declare block)

Please consider following function (formatted I would like to see):

Code:

CREATE OR REPLACE FUNCTION add_polozka()
RETURNS TRIGGER AS
$BODY$
DECLARE
    TEAM1     VARCHAR(100));
    TEAM2     VARCHAR (100);
    TEAM3     VARCHAR (100);
    TEAM4     VARCHAR (100);
    T_COUNT   INTEGER;
BEGIN


now result of SA formatting:
Code:

CREATE OR REPLACE FUNCTION add_polozka
(
)
RETURNS TRIGGER AS
        $BODY$
        DECLARE
        TEAM1 VARCHAR
(
   100   
);

TEAM2 VARCHAR (100);
TEAM3 VARCHAR (100);
TEAM4 VARCHAR (100);
T_COUNT INTEGER;
BEGIN


So let's list issues
1. in case function has no arguments, there is need to break parentheses out of function name
2. $BODY$ tag shouldn't be indented
3. DECLARE tag shouldn't be indented
3. Position of TEAM1 variable is proper, but breaking it's parameters into a few lines is unjustfied. Frankly, datatype of another TEAM variables are formatted properly
4. Other variables in declare block are not indented
5. variables in declare block are not formatted in 2 columns (name-datatype). Similar formatting as for function arguments, function table-type return definition or create table should be applied

Another ones:
6. SELECT formatting template doesn't contain LIMIT and OFFSET
7. It seems that Align Assignments option doesn't work Enabled in config). At least not for independent variables in sql function. for example
Code:

    short_var = 20;
    very_long_variable = 10

is never formatted into
Code:

    short_var               = 20;
    very_long_variable = 10;


Two suggestions more:
1. please make SA config to remember the last active tab, configuration revised. For example while tuning up the formating options, it's really annoying to chose Code Formatting and then particular formatting style.
2. Please consider extending formatting templates for options which should allow to change formatting style depending on current sql statement lenght, number etc.
For example if I select single value, I don't want to beak SELECT line, then It can be:
SELECT col
INTO var
FROM sometable
But in case of more columts selected I woul stay in multiline list:
Code:

SELECT col1,
            col2,
            col3
FROM    table

Similar thing with function arguments. If function has 1 argument, there is no reason to list arguments in column.
I know I can prepare more styles and switch between them but would be very comfy to be able to define optional behaviours in single formatting style
I can imagine it can be beyond SA roadmap, so it's just idea

with regards
Wed Mar 16, 2016 12:07 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Reply with quote
Thank you for your feedback. I have submitted a couple of enhancement requests based on your feedback.

There are couple other things that I wanted to ask and share.
Quote:
...it's really annoying to chose Code Formatting and then particular formatting style...

Are you aware of the Tests... function that allows you to test your formatting rules without leaving the the code formatting space in the Options? It's in the right top corner. And just in case, the Test dialog is non-modal, you can continue making tweaks in the formatting rules and test immediatelly

Quote:
Please consider extending formatting templates for options which should allow to change formatting style depending on current sql statement lenght, number etc.

While not exactly how you describe it, you can kind of do that already, you can define multiple formatting styles and apply them dynamically, highlight the text you want to format (may try using Ctrl+Alt+H multiple times to select container statements recursively) and then press Ctrl+Shift+F11 which would give you a choice to choose the style to apply.


Lastly, I have some suggestions for how to format the code you referenced in the exampled

1. add new formatting rule called create trigger function with the following pattern

Code:
CREATE ... FUNCTION ... ()
RETURNS TRIGGER AS
$body$
   <stmtList>;
$body$
LANGUAGE ...;


2. Change declare rule to

Code:
DECLARE
   <stmtList>;


3. Update begin rule to

Code:
BEGIN
   <stmtList>;
EXCEPTION...
   WHEN ... THEN
      <stmtList>;
END;




All that will not do exactly what you want, but it will get you close to that.
Wed Mar 16, 2016 9:10 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
Thanx for tips. Knowing hotkey for formatting styles makes things easier.

Also your formating templates partialy makes the job. But still it's not perfect, moreover it put emply line between variables without reason. Let;s check my example from above:

Code:
CREATE OR REPLACE FUNCTION add_polozka()
RETURNS TRIGGER AS
$BODY$
DECLARE
    TEAM1     VARCHAR (100);
    TEAM2     VARCHAR (100);
    TEAM3     VARCHAR (100);
    TEAM4     VARCHAR (100);
    T_COUNT   INTEGER;
BEGIN


And the result of formatting is:

Code:
CREATE OR REPLACE FUNCTION my_function ()
RETURNS TRIGGER AS
$BODY$
DECLARE
TEAM1 VARCHAR(100);

TEAM2 VARCHAR(100);
TEAM3 VARCHAR(100);
TEAM4 VARCHAR(100);
T_COUNT INTEGER;
BEGIN


This behaviour is confirmed for recent 7.5.501.
Version 7.5.499 worked even more strange. First formatting attempt did move variables to the left, then second run did add an empty line.

BTW I think we need some formatter tag which would say to engine: "format into columns". While using it, formatter should do the best to format source code into columns (like function arguments etc)
Tue Mar 22, 2016 8:53 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.