Remember in LISP macros you could have those back-quotes to place evaluated expressions into larger string constants?

Likewise, javascript has the `backtick quoting where you can place ${2 * 5} expressions` in which get evaluated to form the final string (here: "backtick quoting where you can place 10 expressions").

In PostgreSQL we have the dollar-quoting which is useful, as is backtick quoting, to write string literals with newlines and all, usually used for encapsulated program text (as in CREATE FUNCTION, etc.)

It would be immensely useful if we could have some unquote syntax inside the dollar quoted strings. Such as, for instance marked with \{ } (which should be backwards compatible if we were to allow the opening $-quote to have an E preceding it to form an escape-string.

Then I could write:

select E$$Lorem ipsum \{a * b} blah blah$$
  from (select 2 a, 5 b) x

to get the string "Lorem ipsum 10 blah blah", and this would be extremely useful when we use the database to generate strings, including to generate executable SQL or PLpgsql bodies.

I know this feature does not currently exist. But what I don't know is if it was ever discussed or considered? I could not find anything by web search.

CodePudding user response：

That's pretty much what format() will do:

select format($$Lorem ipsum %s blah blah$$, a * b)
  from (select 2 a, 5 b) x

Especially if you use SQL to generate SQL, you should use format() together with the placeholder for identifiers: %I and string literals %L to get them quoted properly.