Create a function declaring a predefined text array Create a function declaring a predefined text array postgresql postgresql

Create a function declaring a predefined text array


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


@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.