2

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';

1 Answer 1

2

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.

3
  • I'll still upvote, but I can't take myself to choosing that. =( Oh well, needed it for this, dba.stackexchange.com/a/175841/2639 Interestingly pg_get_functiondef returns the definition with the escaped new lines. Jun 9, 2017 at 1:56
  • 1
    @EvanCarroll see edit Jun 9, 2017 at 1:57
  • Better, move that self-destructive function to the bottom. That's a horrible idea. ;) Jun 9, 2017 at 1:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.