0

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.

Gunther Schadow
  • 1,490
  • 13
  • 22
  • 1
    Note that a question like "*has feature x ever been discussed*" are better asked on the [PostgreSQL mailing list](https://www.postgresql.org/list/) –  Jan 23 '23 at 12:52

1 Answers1

3

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.

  • Thanks for this reminder. I would only say that while it is perhaps a better option than concatenating strings, the necessity to move all expressions out to the end and refer to them via positional references gets messy pretty quickly when you have a longer text with a dozen or more computed expressions. So placing the expressions into back-quote in the middle of the text would be better. Javascript agreed to this position by not having a printf method but backtick quotes with ${expr} escapes. – Gunther Schadow Jan 23 '23 at 21:53