How to return array of values from PostgreSQL function by INT id

Question

I am trying to create a simple PostgreSQL function, where by using INT parameter I like to get array back. The example below will not work, but shall give idea of what I try to get back from a function. Thanks.

CREATE OR REPLACE FUNCTION contact_countries_array(INT)
    RETURNS ANYARRAY AS '
    SELECT ARRAY[contacts_primarycountry, contacts_othercountry] FROM contacts WHERE contacts_id = $1'
    LANGUAGE SQL;

The data type of contacts_primarycountry and contacts_othercountry is integer. contacts_id is unique and integer.

Answer 1

Per the docs:

It is permitted to have polymorphic arguments with a fixed return type, but the converse is not.

As such, I think your attempt to return anyarray won't work.

Your fields look like text, so I think if you altered it to something like this, it would work:

CREATE OR REPLACE FUNCTION contact_countries_array(INT)
RETURNS text[] AS $$
  select array[contacts_primarycountry::text, contacts_othercountry::text]
  FROM contacts WHERE contacts_id = $1
$$
LANGUAGE SQL;

This should compile, and it might work, but I'm honestly not sure:

CREATE OR REPLACE FUNCTION contact_countries_array(anyelement)
RETURNS anyarray AS $$
  select array[contacts_primarycountry::text, contacts_othercountry::text]
  FROM contacts WHERE contacts_id = $1
$$
LANGUAGE SQL;

I think the datatypes would have to match perfectly for this to work, unless you did casting.

Answer 2

Declaring Array, Looping, Adding items to Array, Returning Array with Postgres Function,

You can declare INTEGER array instead of TEXT and avoid casting (counter::TEXT) as well as return type TEXT[]. (Added those for reference.)

CREATE OR REPLACE FUNCTION "GetNumbers"(maxNo INTEGER) RETURNS TEXT[] AS $nums$

DECLARE 
    counter INTEGER := 0;
    nums TEXT[] := ARRAY[]::TEXT[];

BEGIN
    LOOP
        EXIT WHEN counter = maxNo;
        counter = counter + 1;
        nums = array_append(nums, counter::TEXT);
    END LOOP;

    RETURN nums;
END ;

$nums$ LANGUAGE plpgsql;

SELECT "GetNumbers"(5); -- {1,2,3,4,5}