Open duongthienlee opened 2 years ago
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.
I could really use this as well.
Bumping because I would also really appreciate this feature.
+1
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.
$$
instead of //
as a delimiter to eliminate the space introduced which produces / /
. /opt/node-v16.15.0-linux-x64/bin/sql-formatter -c sql-formatter.json |\
sed 's/\sdelimiter.*/\ndelimiter ;/g'
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;
@karlhorky the $$ delimited strings in postgres are supported. You just need to specify postgresql as the language to be formatted. See the FAQ.
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;
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.
Separate feature request for formatting SQL in SQL here:
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 $$
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).
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
Is there a setting like "sql-formatter.dialect"
which could be set to mariadb/mysql?
See the readme: https://github.com/sql-formatter-org/sql-formatter#configuration-options
Ok. but it seems not to include the DELIMITER syntax
That's right. There is no support for DELIMITER. Which is also pretty clearly stated in README.
and that is going to change sometime?
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.
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;
@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.
$$
choice for delimiter, but I've used other strings to no avail.drop procedure...
statementbefore_01
sample code mimics my implementation. I think I'm done messing with this for a bit. before_03
and before_04
examples worked 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 */
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.
Sure. You can try it on the demo page. But SQL Formatter does not handle procedural SQL well.
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 abovethe $$ 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".
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
It returns this