Feature request: Support for delimiter

[duongthienlee]

Hi, thank you for creating such a great library.
It would be great if there is an option that we can specify the delimiter so that the formatter can ignore formatting it.
With current formatter, with the following sql text

DELIMITER //

CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
  DECLARE x TINYINT;
  SET x = 42;
  RETURN x;
END 
//

DELIMITER ; 

It returns this

DELIMITER / /
CREATE
   FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
   DECLARE x TINYINT;

SET x = 42;

RETURN x;

END / / DELIMITER;

[nene]

Currently the only option on table is to don't touch the formatting of anything between DELIMITER // and DELIMITER ;.

Though really even this is not near the list of features considered for now.

But I'll leave this issue open to see how much interest there even is in such a feature.

[mattsbennett]

I could really use this as well.

[grantwforsythe]

Bumping because I would also really appreciate this feature.

[zinw]

+1

[carywreams]

would also appreciate this. -- started using CTEs to eliminate duplicated code in stored procedures. want to use sql-formatter at the end of the build process to format the final stored proc.

my work-around

• use $$ instead of // as a delimiter to eliminate the space introduced which produces / /.
• use sed to clean up the final END sequence:

/opt/node-v16.15.0-linux-x64/bin/sql-formatter -c sql-formatter.json |\
 sed 's/\sdelimiter.*/\ndelimiter ;/g'  

[karlhorky]

Would be great to have support for formatting PL/pgSQL in $$ delimiters, eg. the following PostgreSQL function example code from the PostgreSQL docs:

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

[nene]

@karlhorky the $$ delimited strings in postgres are supported. You just need to specify postgresql as the language to be formatted. See the FAQ.

[karlhorky]

Oh I meant the PL/pgSQL inside of the $$ - but I guess this is involved, because it's a different language.

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
                  BEGIN
         RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

Formatted:

CREATE
OR REPLACE FUNCTION increment (i integer) RETURNS integer AS $$
                  BEGIN
         RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

Expected:

CREATE
OR REPLACE FUNCTION increment (i integer) RETURNS integer AS $$
  BEGIN
    RETURN i + 1;
  END;
$$ LANGUAGE plpgsql;

[nene]

It's even more involved than that, because $$-delimited strings can be used for anything in Postgres. And even in Create function statement it's possible to have other languages besides pl/pgsql.

In general, formatting of procedural SQL is not really supported in SQL Formatter. Properly formatting that is really not achievable with the current architecture.

See readme for information about another formatting library, which does support that, though not yet for Postgres.

[karlhorky]

Separate feature request for formatting SQL in SQL here:

• Feature Request: SQL in SQL prettification #680

[theking2]

would also appreciate this. -- started using CTEs to eliminate duplicated code in stored procedures. want to use sql-formatter at the end of the build process to format the final stored proc.

my work-around

* use `$$` instead of `//` as a delimiter to eliminate the space introduced which produces `/ /`.

* use sed to clean up the final END sequence:

/opt/node-v16.15.0-linux-x64/bin/sql-formatter -c sql-formatter.json |\
 sed 's/\sdelimiter.*/\ndelimiter ;/g'  

No it doesn't. It gives a parse error on $$

[karlhorky]

Show off-topic message

No it doesn't. It gives a parse error on $$

@theking2 can you provide a demo where this is failing? It's working with latest sql-formatter version for our projects.

One note: you need to make sure to specify the postgresql language, as @nene mentioned in his comment above

the $$ delimited strings in postgres are supported. You just need to specify postgresql as the language to be formatted. See the FAQ.

[nene]

Well, I for one would be surprised if it does work.

I suspect @karlhorky is talking about $$-delimited strings in Postgres, not about changing statement delimiter from ; to something else (which this issue is really about).

[karlhorky]

I suspect @karlhorky is talking about $$-delimited strings in Postgres, not about changing statement delimiter from ; to something else (which this issue is really about).

Oh sorry, I am mixing things here. Disregard my message above! I've collapsed it

[theking2]

Is there a setting like "sql-formatter.dialect" which could be set to mariadb/mysql?

[nene]

See the readme: https://github.com/sql-formatter-org/sql-formatter#configuration-options

[theking2]

See the readme: https://github.com/sql-formatter-org/sql-formatter#configuration-options

Ok. but it seems not to include the DELIMITER syntax

[nene]

That's right. There is no support for DELIMITER. Which is also pretty clearly stated in README.

[theking2]

and that is going to change sometime?

[nene]

Unlikely. Though I'm open to pull requests if anybody wishes to tackle this problem. But as you can see, this issue was opened in 2021.

Also, I'm personally not planning any real feature development on this library. Instead concentrating on developing prettier-plugin-sql-cst, which for now also doesn't support delimiter changing. Though I'm more likely to implement such feature there rather than here.

[kingma-sbw]

Show off-topic message

DROP Procedure IF EXISTS `Test`;

DELIMITER $$
CREATE /* DEFINER=`zeugnis_nm_dev`@`localhost` */ PROCEDURE `Test`()
    NO SQL
Select 1$$
DELIMITER ;

becomes

DROP Procedure
  IF EXISTS `Test`;


DELIMITER $$ CREATE
/* DEFINER=`zeugnis_nm_dev`@`localhost` */
PROCEDURE `Test`() NO SQL
Select
  1 $$ DELIMITER;

[carywreams]

@theking2 ,

my workaround isn't working any more for me. I'm now stuck on sql-formatter pulling the create statement up to the same line as the delimiter declaration (when the delimiter is declared as $$) -- a seemingly new behavior with my change to v15.0.2 ( I had been using something with node v16). I'll see what I can do, as I'll be actively working to get sql-formatter back into my workflow.

• update 1
  • seems now that only a semi-colon can preserve a new line
  • i.e., without a semi-colon at the end of a line, lines shall be combined by sql-formatter
  • I first thought this was a function of the $$ choice for delimiter, but I've used other strings to no avail.
  • ...still working on it.
• update 2
  • moving my updates on this to my own repo
  • fair warning: my solution (if possible) will likely be a silly-putty and duct tape hack to keep sql-formatter in my workflow. It may or may not be suitable for anyone else.
  • reference
• final update
  • experimented a bit; had to learn that a simple statement could be used instead of a compound statement in a stored procedure definition
  • managed to get it to work if I expanded use of the delimiter to include the drop procedure... statement
  • my mre repo should be public, if you want to take a look at the variants. the before_01 sample code mimics my implementation. I think I'm done messing with this for a bit.
  • both the before_03 and before_04 examples worked

[nene]

I implemented a new feature that allows for a cleaner work-around for issues like this.

In short, you can wrap the problematic SQL between special comments that turn off the formatter:

/* sql-formatter-disable */
DELIMITER //

CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
  DECLARE x TINYINT;
  SET x = 42;
  RETURN x;
END 
//

DELIMITER ; 
/* sql-formatter-enable */

[kingma-sbw]

Could this be written thusly?:

/* sql-formatter-disable */
DELIMITER //
/* sql-formatter-enable */
CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
  DECLARE x TINYINT;
  SET x = 42;
  RETURN x;
END 
/* sql-formatter-disable */
//
DELIMITER ; 
/* sql-formatter-enable */

The CREATE FUNCTION is "normal" SQL.

[nene]

Sure. You can try it on the demo page. But SQL Formatter does not handle procedural SQL well.

[theking2]

Show off-topic message

No it doesn't. It gives a parse error on $$

@theking2 can you provide a demo where this is failing? It's working with latest sql-formatter version for our projects.

One note: you need to make sure to specify the postgresql language, as @nene mentioned in his comment above

the $$ delimited strings in postgres are supported. You just need to specify postgresql as the language to be formatted. See the FAQ.

I cannot use postgressql at it fails on this:

Unable to format SQL:
Error: Parse error: Unexpected "`post_proc" at line 1 column 26.
SQL dialect used: "postgresql".

[kingma-sbw]

I found a workarout using:
DELIMITER #

This is a valid (comment) token and gets overwriten as delimiter.

DROP Procedure IF EXISTS `add`;

DELIMITER #
CREATE
/* DEFINER=`minidwh`@`localhost` */
PROCEDURE `add`(IN `a` INT, IN `b` INT, OUT `result` INT) BEGIN
set
  result = a + b;

END #
DELIMITER;

is formatted