Hello,
I'm using SQLDeveloper 18.4
With following format options selected:
What I find really odd is the inconsistency of indentation in MERGE statements compared to INSERT/UPDATE statements.
Some parts of MERGE can go quite far to the right making it hard to read the code, specially when MERGE is already indented inside PLSQL.
Here is how it is today:
insert into a_table (
a,
b,
c
) values (
1,
'SOME VAL',
3
);
merge into some_really_really_long_table_name using (
select
\*
from
another\_table
)
on ( source_column1 = target_column1
and source_column2 = target_column2 )
when not matched then insert (
target_column3,
target_column3,
some_long_column_name ) values (
source_column3,
source_column4,
source_column5 )
when matched then update set some_long_column_name = source_column3,
target\_column4 = source\_column4,
target\_column5 = source\_column5;
merge into a_table using (
select
\*
from
another\_table
)
on ( source_column1 = target_column1
and source_column2 = target_column2 )
when not matched then insert (
target_column3,
target_column3,
target_column5 ) values (
source_column3,
source_column4,
source_column5 )
when matched then update set target_column3 = source_column3,
target\_column4 = source\_column4,
target\_column5 = source\_column5;
update a_table
set
some_column = a_value,
some_column = a_value,
some_column = a_value,
some_column = a_value
where
another_column = a_another_value;
What I would like to see however is more like this:
insert into a_table (
a,
b,
c )
values (
1,
'SOME VAL',
3 );
merge into some_really_really_long_table_name
using (
select
\*
from
another\_table
)
on ( source_column1 = target_column1
and source_column2 = target_column2 )
when not matched then
insert (
target\_column3,
target\_column3,
some\_long\_column\_name )
values (
source\_column3,
source\_column4,
source\_column5 )
when matched then update
set some_long_column_name = source_column3,
target\_column4 = source\_column4,
target\_column5 = source\_column5;
merge into a_table
using (
select
\*
from
another\_table
)
on ( source_column1 = target_column1
and source_column2 = target_column2 )
when not matched then
insert (
target\_column3,
target\_column3,
target\_column5 )
values (
source\_column3,
source\_column4,
source\_column5 )
when matched then update
set target_column3 = source_column3,
target\_column4 = source\_column4,
target\_column5 = source\_column5;
update a_table
set some_column = a_value,
some\_column = a\_value,
some\_column = a\_value,
some\_column = a\_value
where
another_column = a_another_value;
The ask.
- Add line break before following keywords of merge statement and indent them using indentation depth:
using, insert, update, values, set
- keep columns indented after (or under) the set keyword
Rationale:
Current solution is making code hard to read as:
- select in the USING clause is indented differently depending on the table name of the MERGE
- column lists do not fall under the preceding keyword (values/insert/update)
- column lists in update of merge is formatted&indented differently than in standalone update
- list of columns of merge-update goes way too far to the right
- select statement of merge-using goes way too far to the right
The format definition language seems very powerful however I find it too hard to modify myself.