Create a function declaring a predefined text array

arrays function postgresql plpgsql declare
Edson Horacio Junior picture Edson Horacio Junior · Jul 24, 2014 · Viewed 22.8k times · Source

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

pozs picture pozs · Jul 24, 2014

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

Related questions

Return array in a function

I have an array int arr[5] that is passed to a function fillarr(int arr[]): int fillarr(int arr[]) { for(...); return arr; } How can I return that array? How will I use it, say I returned a pointer how am …

Read More
Passing an array as a function parameter in JavaScript

I'd like to call a function using an array as parameters: const x = ['p0', 'p1', 'p2']; call_me(x[0], x[1], x[2]); // I don't like it function call_me (param0, param1, param2 ) { // ... } Is there a better way of …

Read More
PHP append one array to another (not array_push or +)

How to append one array to another without comparing their keys? $a = array( 'a', 'b' ); $b = array( 'c', 'd' ); At the end it should be: Array( [0]=>a [1]=>b [2]=>c [3]=>d ) If I use something like [] or …

Read More