Escaping to return `TEXT[]` type with a `RETURN QUERY EXECUTE` and `VALUES` statement in PostgreSQL

Question

I'm trying to return TEXT[] type from my_func() with a RETURN QUERY EXECUTE and VALUES statement.

But calling my_func() which has " in '' got the error as shown below:

CREATE FUNCTION my_func() RETURNS SETOF TEXT[] AS $$
BEGIN               -- ↓              ↓ ↓ ↓ ↓           ↓ ↓ ↓ ↓  ↓
  RETURN QUERY EXECUTE 'VALUES (ARRAY["a","b"]), (ARRAY["c","d"])';
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT * FROM my_func();
ERROR:  column "a" does not exist
LINE 1: VALUES (ARRAY["a","b"]), (ARRAY["c","d"])
                      ^
QUERY:  VALUES (ARRAY["a","b"]), (ARRAY["c","d"])
CONTEXT:  PL/pgSQL function my_func() line 3 at RETURN QUERY

And, calling my_func() which has ' in "" got the error as shown below:

CREATE FUNCTION my_func() RETURNS SETOF TEXT[] AS $$
BEGIN               -- ↓              ↓ ↓ ↓ ↓           ↓ ↓ ↓ ↓  ↓
  RETURN QUERY EXECUTE "VALUES (ARRAY['a','b']), (ARRAY['c','d'])";
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT * FROM my_func();
ERROR:  column "VALUES (ARRAY['a','b']), (ARRAY['c','d'])" does not exist
LINE 1: "VALUES (ARRAY['a','b']), (ARRAY['c','d'])"
        ^
QUERY:  "VALUES (ARRAY['a','b']), (ARRAY['c','d'])"
CONTEXT:  PL/pgSQL function my_func() line 3 at RETURN QUERY

Actually, I could return TEXT[] type from my_func() with a RETURN QUERY and VALUES statement without error as shown below:

CREATE FUNCTION my_func() RETURNS SETOF TEXT[] AS $$
BEGIN
  RETURN QUERY VALUES (ARRAY['a','b']), (ARRAY['c','d']);
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT * FROM my_func();
 my_func
---------
 {a,b}
 {c,d}
(2 rows)

So, how can I return TEXT[] type from my_func() with a RETURN QUERY EXECUTE and VALUES statement?

Answer 1

Escaping ' in '' with these ways below, you can return TEXT[] type from my_func() with a RETURN QUERY EXECUTE and VALUES statement as shown below:

CREATE FUNCTION my_func() RETURNS SETOF TEXT[] AS $$
BEGIN                              -- ↓  ↓  ↓  ↓            ↓  ↓  ↓  ↓
  RETURN QUERY EXECUTE 'VALUES (ARRAY[''a'',''b'']), (ARRAY[''c'',''d''])';
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION my_func() RETURNS SETOF TEXT[] AS $$
BEGIN               -- ↓               ↓  ↓  ↓  ↓            ↓  ↓  ↓  ↓
  RETURN QUERY EXECUTE E'VALUES (ARRAY[\'a\',\'b\']), (ARRAY[\'c\',\'d\'])';
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() returns 2 rows as shown below:

postgres=# SELECT * FROM my_func();
 my_func
---------
 {a,b}
 {c,d}
(2 rows)

Answer 2

Use named/tagged dollar $x$ quotes outside, and regular single-quotes inside: demo

CREATE OR REPLACE FUNCTION my_func() RETURNS SETOF TEXT[] AS $f$
BEGIN               -- ↓                ↓ ↓ ↓ ↓
  RETURN QUERY EXECUTE $q$VALUES (ARRAY['a','b']), 
                                 (ARRAY['c','d'])
                       $q$;
END;
$f$ LANGUAGE plpgsql;

SELECT * FROM my_func();

my_func
{a,b}
{c,d}