How do you unescape "C-like backslash escape sequence" that come from a function?

Question

This will unescape the \n\n sequence.

SELECT E'\n\nFOO';

But if that same sequence comes from a function, what do you do.

CREATE FUNCTION asdf() RETURNS text
AS $$
  SELECT '\n\nFOO'::text
$$ LANGUAGE 'sql';

Answer 1

Pg doesn't appear to expose its string parsing/lexing to SQL. It has quote_literal etc but doesn't seem to have the reverse. So I'm not aware of another way to do this without breaking out into a PL or writing a simple C extension.

e.g.

CREATE OR REPLACE FUNCTION pyunquote(quoted text) RETURNS text AS $$
return quoted.decode("string-escape")
$$ LANGUAGE plpythonu;

then

test=# SELECT pyunquote('\n\nAAA');
 pyunquote 
-----------
          +
          +
 AAA
(1 row)

but note that this uses Python's string escape rules, not PostgreSQL's.

To get PostgreSQL's rules, you could use the following horribly insecure approach and trust whatever the function passes you:

DO
LANGUAGE plpgsql
$$
DECLARE
  x text;
BEGIN
  EXECUTE format('SELECT E''%s''', '\n\nFOO') INTO x;
  RAISE NOTICE 'value is "%"', x;
END;
$$;

but of course someone can pass ';DROP TABLE customer;-- which won't be fun.