Create a function declaring a predefined text array

Question

I need to create a function in Postgres and one of the variables I declare is a predefined text array, but I don't know the syntax to set its values. This is what I have so far:

CREATE OR REPLACE FUNCTION testFunction() RETURNS text
AS $$
DECLARE
    TESTARRAY TEXT['value 1', 'value 2', 'value 3'];
BEGIN
    return 'any text';
END;
$$ LANGUAGE 'plpgsql';

I get this error when I execute the code:

ERROR:  syntax error at or near "'value 1'"
LINE 5: TESTARRAY TEXT['value 1', 'value 2', 'value 3'];

Answer 1

@pozs already gave a proper answer.

In addition, when in doubt about proper syntax, you can just ask Postgres for the string literal:

test=# SELECT ARRAY['value 1', 'value 2', 'value 3'];
              array
---------------------------------
 {"value 1","value 2","value 3"}

test=# SELECT  ARRAY['foo', 'bar', 'b A "Z'];
        array
---------------------
 {foo,bar,"b A \"Z"}

There is a string representation for every possible value of every type.

To get the readily quoted version, that deals with all possible corner cases, wrap it in quote_nullable():

test=# SELECT quote_nullable(ARRAY['value 1', 'value 2', 'value 3']);
          quote_nullable
-----------------------------------
 '{"value 1","value 2","value 3"}'

test=# SELECT quote_nullable(ARRAY['foo', 'bar', 'b ''A'' "Z"']);
         quote_nullable
--------------------------------
 E'{foo,bar,"b ''A'' \\"Z\\""}'

Your example:

CREATE OR REPLACE FUNCTION test_function()
  RETURNS text AS
$func$
DECLARE
   testarray text[] := '{"value 1","value 2","value 3"}';
BEGIN
   RETURN 'any text';
END
$func$ LANGUAGE plpgsql;

Other points

• Don't quote the language name: LANGUAGE plpgsql.

• Use lower case identifiers in Postgres. Per documentation:

  All key words are case-insensitive. Identifiers are implicitly converted to lower case unless double-quoted, just as they are in ordinary SQL commands.

Answer 2

The right syntax for default value (in a variable declaration) is { DEFAULT | := } expression.

For expression, you can use any of the array inputs.

F.ex. these can work:

DECLARE
  test1  TEXT ARRAY  DEFAULT  ARRAY['value 1', 'value 2', 'value 3'];
  test2  TEXT[]      :=       '{"value 1", "value 2", "value 3"}';
  test3  TEXT[]      DEFAULT  ARRAY[]::TEXT[]; -- empty array-constructors need a cast