Merging and returning arrays

Question

I've two functions,

CREATE OR REPLACE FUNCTION function_a(input varchar)
RETURNS setof integer AS $$
BEGIN
  RETURN QUERY
    SELECT somecolumn FROM some_things WHERE a_column = input;
END;
$$ LANGUAGE PLpgSQL;


CREATE OR REPLACE FUNCTION function_b(inputs varchar[])
RETURNS setof integer AS $$
DECLARE
  input varchar;
  result integer[];
BEGIN
  FOREACH input IN ARRAY inputs LOOP
    result := result || ARRAY[function_a(input)];
  END LOOP;
END;
$$ LANGUAGE PLpgSQL;

I am running it like,

 SELECT function_b(ARRAY['a', 'b']);

The error,

ERROR:  query "SELECT result || ARRAY[function_a(input)]" returned more than one row
CONTEXT:  PL/pgSQL function function_b(character varying[]) line 7 at assignment

All I want to do is to run a function over an array. I've always used scripting languages like Ruby to do this kind of stuff instead of using SQL, but I'm trying to learn SQL as it is much faster to get results on the db console itself. I wish it wasn't so frustrating.

klin · Accepted Answer · 2019-01-31 04:47:56Z

First, in SQL we mainly write queries. You can do the same with a simple query:

select somecolumn 
from some_things 
where a_column = any(array['a', 'b']);

If you need a function, it may be an SQL one:

create or replace function sql_function(inputs text[])
returns setof integer language sql as $$
    select somecolumn 
    from some_things 
    where a_column = any(inputs);
$$;

SQL functions are simpler and usually faster than plpgsql ones.

Back to your function_b() - the array constructor should look like this:

ARRAY(SELECT function_a(input))

Note also that the function does not return anything. Because you aggregate results in an array, you should unnest it to return rows:

CREATE OR REPLACE FUNCTION function_b(inputs varchar[])
RETURNS setof integer AS $$
DECLARE
  input varchar;
  result integer[];
BEGIN
  FOREACH input IN ARRAY inputs LOOP
    result := result || ARRAY(SELECT function_a(input));
  END LOOP;
  RETURN QUERY SELECT unnest(result);
END;
$$ LANGUAGE PLpgSQL;

Kaushik Nayak · Accepted Answer · 2019-01-31 04:51:20Z

0

You don't need the first function at all. I believe you want to return a set of integers from the table for the given input. A function like this is all you need.

CREATE OR REPLACE FUNCTION function_b(inputs varchar[])
RETURNS setof integer AS $$
BEGIN
       RETURN QUERY SELECT somecolumn 
          FROM some_things WHERE a_column = ANY ( inputs );
END;
$$ LANGUAGE PLpgSQL;

Demo

answered Jan 31, 2019 at 4:51

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

Merging and returning arrays

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related