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.