Enhancement: SQL in SQL prettification

[innermatrix]

I am guessing that you will probably find this to be out of scope of your plugin, but maybe not, so I figure I'd ask:

If you are writing SQL functions, it's pretty common to have SQL embedded inside SQL; for example (straight out of Postgres documentation):

CREATE FUNCTION dup(int) RETURNS dup_result
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

In this case, $$ is a string quoting delimiter, and SELECT $1, CAST($1 AS text) || ' is text' is a quoted string.

The issue is that most SQL formatters (and in particular, the one used by prettier-plugin-embed do not recurse into these quoted string function bodies, so the function declaration is prettified but the function implementation remains ugly. Here's what I get if I run the above through prettier with prettier-plugin-sql (regardless of whether prettier-plugin-embed is on):

create function dup (int) returns dup_result as $$ SELECT $1, CAST($1 AS text) || ' is text' $$ language sql;

What I wish I got would be more like

create function dup (int) returns dup_result as $$ 
    select
        $1,
        cast($1 as text)||' is text'
$$ language sql;

(That's what I get if I manually run the function body through prettier-plugin-sql and then paste it inside $$ delimiters.

Now, on the one hand, I realize that the description of prettier-plugin-embed specifically says that its only intent is to handle tagged strings in TS/JS, so if you just want to turn this idea away, I get that. But if you're looking for another cool thing to do in the realm of embedded string prettification, well, then I think this one is a good candidate 🙂

[Sec-ant]

If language A can embed language B (A and B can be the same language), then the formatting support of the embedded B should be added in the plugin of A, because the AST/CST information of A is needed to recognize an embedded node. Prettier has a dedicated API embed for plugins to do this.

So I think this should be a feature request for prettier-plugin-sql. cc @JounQin

[innermatrix]

Gotcha, ty for explaining

[JounQin]

You should post a feature request at https://github.com/sql-formatter-org/sql-formatter or https://github.com/nene/prettier-plugin-sql-cst.

[innermatrix]

sql-formatter-org/sql-formatter#680

[Sec-ant]

There's nothing we can do in this repo currently, so let's track this feature in the following links:

• Feature Request: SQL in SQL prettification sql-formatter-org/sql-formatter#680
• https://github.com/nene/prettier-plugin-sql-cst
• https://github.com/nene/sql-parser-cst