Repository: sql-formatter-org/sql-formatter
Pull request #655 (2023-11-01 06:05:55)

fix: Update E style escape strings for postgresql
As described in the [wiki](https://github.com/sql-formatter-org/sql-formatter/wiki/strings), E style PostgreSQL strings support both backslash and double single quote escaping. Also added the additional test for E strings into the test util. 


Patch:
diff --git a/src/languages/postgresql/postgresql.formatter.ts b/src/languages/postgresql/postgresql.formatter.ts
index 4ea02dc4..38909b83 100644
--- a/src/languages/postgresql/postgresql.formatter.ts
+++ b/src/languages/postgresql/postgresql.formatter.ts
@@ -266,7 +266,7 @@ export const postgresql: DialectOptions = {
     stringTypes: [
       '$$',
       { quote: "''-qq", prefixes: ['U&'] },
-      { quote: "''-bs", prefixes: ['E'], requirePrefix: true },
+      { quote: "''-qq-bs", prefixes: ['E'], requirePrefix: true },
       { quote: "''-raw", prefixes: ['B', 'X'], requirePrefix: true },
     ],
     identTypes: [{ quote: '""-qq', prefixes: ['U&'] }],


Test patch:
diff --git a/test/features/strings.ts b/test/features/strings.ts
index ee27c489..2bd64710 100644
--- a/test/features/strings.ts
+++ b/test/features/strings.ts
@@ -12,6 +12,7 @@ type StringType =
   | "''-bs" // with backslash escaping
   | "U&''" // with repeated-quote escaping
   | "N''" // with escaping style depending on whether also ''-qq or ''-bs was specified
+  | "E''" // with escaping style depending on whether also ''-qq or ''-bs was specified
   | "X''" // no escaping
   | 'X""' // no escaping
   | "B''" // no escaping
@@ -139,6 +140,32 @@ export default function supportsStrings(format: FormatFn, stringTypes: StringTyp
     });
   }
 
+  if (stringTypes.includes("E''")) {
+    it('supports unicode strings', () => {
+      expect(format("SELECT E'where' FROM E'update'")).toBe(dedent`
+        SELECT
+          E'where'
+        FROM
+          E'update'
+      `);
+    });
+
+    if (stringTypes.includes("''-qq")) {
+      it("supports escaping in E'' strings with repeated quote", () => {
+        expect(format("E'foo '' JOIN bar'")).toBe("E'foo '' JOIN bar'");
+      });
+    }
+    if (stringTypes.includes("''-bs")) {
+      it("supports escaping in E'' strings with a backslash", () => {
+        expect(format("E'foo \\' JOIN bar'")).toBe("E'foo \\' JOIN bar'");
+      });
+    }
+
+    it("detects consecutive E'' strings as separate ones", () => {
+      expect(format("E'foo'E'bar'")).toBe("E'foo' E'bar'");
+    });
+  }
+
   if (stringTypes.includes("X''")) {
     it('supports hex byte sequences', () => {
       expect(format("x'0E'")).toBe("x'0E'");
diff --git a/test/postgresql.test.ts b/test/postgresql.test.ts
index b3dc4ada..30221211 100644
--- a/test/postgresql.test.ts
+++ b/test/postgresql.test.ts
@@ -52,7 +52,7 @@ describe('PostgreSqlFormatter', () => {
   supportsOnConflict(format);
   supportsUpdate(format, { whereCurrentOf: true });
   supportsTruncateTable(format, { withoutTable: true });
-  supportsStrings(format, ["''-qq", "U&''", "X''", "B''"]);
+  supportsStrings(format, ["''-qq", "U&''", "X''", "B''", "E''"]);
   supportsIdentifiers(format, [`""-qq`, 'U&""']);
   supportsBetween(format);
   supportsSchema(format);