Postgresql function return array

by

The PostgreSQL function can be used to define a custom function that returns an array. Here’s an example:


CREATE OR REPLACE FUNCTION get_numbers() RETURNS INTEGER[] AS $$
DECLARE
  numbers INTEGER[] := ARRAY[1, 2, 3, 4, 5];
BEGIN
  RETURN numbers;
END;
$$ LANGUAGE plpgsql;

In this example, we define a function called “get_numbers” that returns an array of integers. The data type of the return value is specified as “INTEGER[]”.

Inside the function body, we declare a variable called “numbers” and initialize it with an array literal using the “ARRAY” keyword. The elements of the array are the integers 1 to 5.

Finally, we use the “RETURN” statement to return the “numbers” array.

To call the function and retrieve the array, you can use a SELECT statement:


SELECT get_numbers();

This will execute the “get_numbers” function and retrieve the returned array.

You can also use the returned array as part of a larger query. Here’s an example:


SELECT *
FROM my_table
WHERE id = ANY(get_numbers());

In this example, we use the “ANY” operator to check if the “id” column of “my_table” exists in the array returned by the “get_numbers” function. This will return all rows in “my_table” where the “id” matches any element of the array.

Leave a comment