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'];

@ErwinBrandstetter No, I was trying to see what editing was all about and saw a simple thing to change nothing more. If that's stepping on anyone's foot I'll gladly revert it. — Kuberchaun, Jul 24 '14 at 16:17
@Bob We have "too minor" as reason to turn down suggested edits. That should tell you something about the site policy. Also, you are close to 10k reputation, you should know your way around here. Changing a single "i" just makes the post inconsistent. Change all or nothing, please. — Erwin Brandstetter, Jul 24 '14 at 16:19
@Bob: Well, one isn't always able or willing to change *all*. But when you change "i" to "I", you should at least deal with all instance in a short text. And what ever else is obvious to you. Thanks for understanding. :) — Erwin Brandstetter, Jul 24 '14 at 16:26

score 36 · Accepted Answer · answered Jul 24 '14 at 15:58

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

Erwin Brandstetter · Answer 2 · 2022-05-16T23:00:41.743

@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
  LANGUAGE plpgsql AS
$func$
DECLARE
   testarray text[] := '{"value 1","value 2","value 3"}';
BEGIN
   RETURN 'any text';
END
$func$;

Asides

Don't quote the language name: LANGUAGE plpgsql.

Use lower case identifiers in Postgres. The manual:

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

Create a function declaring a predefined text array

2 Answers2

Asides