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.

Please edit your question add some sample data and the expected output based on that data. I have no idea what you are trying to do — user330315
– user330315, Commented Feb 13, 2016 at 0:31

Hambone · Accepted Answer · 2016-02-13 02:23:45Z

16

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.

edited Feb 13, 2016 at 2:23

answered Feb 13, 2016 at 2:18

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Erwin Brandstetter Over a year ago

Yes to the first variant, assuming that contacts.contacts_id is defined UNIQUE. The polymorphic type in the second one is probably unwarranted as the data type of contacts.contacts_id should be known in advance anyway.

Jean Louis Over a year ago

Thank you people. Based on @Hambone, I have tried it like this:

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

and now it works. It returns like {55,81} as 2 possible countries of the contact, that I can compare now to list of countries with {55,81} && {55,82,101,12}, @Hambone, thank you much, @a_horse_with_no_name, @erwin_brandstetter

Eugene Over a year ago

Doesn't it require a return inside function, for it to be a valid code?

Hambone Over a year ago

@Eugene -- not when it's LANGUAGE SQL. You're probably thinking pgplsql

Thushara Buddhika · Accepted Answer · 2019-11-21 03:42:05Z

10

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}

edited Nov 21, 2019 at 3:42

answered Nov 18, 2019 at 5:53

Thushara Buddhika

1,82815 silver badges15 bronze badges

2 Comments

nick w. Over a year ago

The script does not work. Here is an updated version.

CREATE OR REPLACE FUNCTION get_numbers(maxNo INTEGER) RETURNS TEXT[] AS $$ 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 ;  $$ LANGUAGE plpgsql;

Thushara Buddhika Over a year ago

Thank you nick, I updated the function. Now it's correct.

Super Kai - Kazuya Ito · Accepted Answer · 2024-02-12 16:35:29Z

0

For example, you can create my_func() which can return VARCHAR[] type as shown below. *You can replace VARCHAR[] with TEXT[].:

CREATE FUNCTION my_func() RETURNS VARCHAR[]
AS $$                          -- ↑ ↑ ↑ ↑ ↑
SELECT ARRAY['John','David','Robert'];
$$ LANGUAGE SQL;

Then, calling my_func() returns an array as shown below:

postgres=# SELECT my_func();
       my_func
---------------------
 {John,David,Robert}
(1 row)

answered Feb 12, 2024 at 16:35

Super Kai - Kazuya Ito

42.7k23 gold badges257 silver badges256 bronze badges

Comments

Getafix · Accepted Answer · 2024-04-11 22:06:35Z

0

With plpgsql it's slightly different. So for future visitors, here is a sample:

CREATE OR REPLACE FUNCTION public.split_something(des_in text)
RETURNS text[]
 LANGUAGE plpgsql
AS $function$
declare
   p1 text;
   p2 text;
begin
     ---other logic to set values for p1/p2
return array[p1::text, p2::text];
END;
$function$;

answered Apr 11, 2024 at 22:06

Getafix

1512 silver badges7 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related