Question 340611 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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)

  2. 0

    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}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search