$$-quoted strings throw on single quotes

[FelixZY]

Postgres supports dollar quoted strings as an alternative to regular '<text>' strings. This is useful when you need to include the ' character itself but don't want to escape it.

The official documentation can be found here

Unfortunately, pg-minify's parser does not appear to support this.

Steps to reproduce

const minify = require('pg-minify');

const sql = `
comment on schema "public" is $$
  It's an apostrophe!
$$;
`;

console.log(minify(sql));

Expected behavior

comment on schema "public" is $$ It's an apostrophe! $$;

Actual behavior

<src>/node_modules/pg-minify/lib/parser.js:186
        throw new SQLParsingError(code, position);
        ^

SQLParsingError: Error parsing SQL at {line:3,col:5}: Unclosed text block.
    at throwError (<src>/node_modules/pg-minify/lib/parser.js:186:15)
    at minify (<src>/node_modules/pg-minify/lib/parser.js:124:17)
    at Object.<anonymous> (<src>/index.js:9:13)
    at Module._compile (node:internal/modules/cjs/loader:1105:14)
    at Module._extensions..js (node:internal/modules/cjs/loader:1159:10)
    at Module.load (node:internal/modules/cjs/loader:981:32)
    at Module._load (node:internal/modules/cjs/loader:827:12)
    at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:77:12)
    at node:internal/main/run_main_module:17:47 {
  error: 'Unclosed text block.',
  code: 1,
  position: { line: 3, column: 5 }
}

Environment

• Version of pg-minify: 1.6.4
• OS type (Linux/Windows/Mac): Linux
• Version of Node.js: 18.0.0

[vitaly-t]

The correct SQL syntax for setting a comment for PostgreSQL schema is this:

COMMENT ON SCHEMA public IS 'It''s an apostrophe!';

What you use is one wicked way to circumvent SQL syntax for strings. Congratulations for the find, I've never seen anything like this in my many years of working with PostgreSQL!

I'm not sure I will try to do anything for this SQL hack, but it's an interesting one! 😄

[FelixZY]

The dollar quoted syntax is completely valid and even documented specifically for the case of single quotes:

While the standard syntax for specifying string constants is usually convenient, it can be difficult to understand when the desired string contains many single quotes, since each of those must be doubled. To allow more readable queries in such situations, PostgreSQL provides another way, called “dollar quoting”, to write string constants.

I very much disagree with the definition of a "hack" or that this would not be the correct syntax for setting a comment in PostgreSQL.

[vitaly-t]

So, it is a pure PostgreSQL hack, but this is far from normal. I've never seen anyone writing SQL like this.

You may as well write all your SQL queries like that -

update users set login = $$ old'one $$ where login = $$ new'one $$

But that's just trying to work-around bad SQL, IMO. I have marked it as a bug, but I'm not sure if I will do anything to fix this. Maybe later. It's never come up after 8 years of using this library. But, thanks for the find!

In the meantime, just use the standard SQL for strings, where each single quote is doubled when inside a string ;)

[vitaly-t]

According to this, dollar-quoting can be either the start of a function body or it can be a non-normalized string. This library has no way of telling which one it is.

In the current implementation, this library simply ignores $$, and parses everything inside it as valid SQL. If we assume it is a non-escaped string instead, it means we need to deactivate the parsing. But as I wrote above, we have no way of telling which one it is. And this is why defining strings via $$ is generally not recommended, because of the mixed interpretation of what it is inside $$ block.

I am afraid that in such a situation, we can only document dollar strings as unsupported. I just do not see a way of how to detect them and differentiate from a valid function body. This library doesn't have such intelligence, only PostgreSQL can deduce it, based on a preceding function declaration header, which is something that would be quite an overhead for this library to try to figure out. We do not really analyze the SQL logic here, we just do basic syntax parsing.

[FelixZY]

Alright 🙁 thanks for looking into it 👍

[vitaly-t]

Added the following section in 1.6.5 update:

---

I am not considering to do anything further about it presently, so closing the issue. Maybe somebody will come up with an idea. Leaving it until then.