How do I pass in the array output of SQL query into a PostgreSQL (PL/pgSQL) function?

Question

I am able to do the following in SQL where an "array" of user_ids are passed into the where clause of a SQL query.

select * from users where id in (select user_id from profiles);

I would like to do the same thing but pass the "array" into a PostgreSQL (PL/pgSQL) function as shown below. How do I declare the function and work with the "array" within the function?

select * from users_function(select user_id from profiles);

CREATE OR REPLACE FUNCTION users_function(....)
  RETURNS void AS
$BODY$
....

nate c · Accepted Answer · 2011-02-16 03:04:24Z

2

Declare an array datatype [] in the function then use the aggregate function array_agg to transform the select statement into an array.

CREATE OR REPLACE FUNCTION users_function(myints integer[])
$$
 BEGIN
      -- you need to find the bounds with array_lower and array_upper
  FOR i in array_lower(myints, 1) .. array_upper(myints, 1) LOOP
     Raise Notice '%', myints[i]::integer;
  END LOOP;
 END;
$$

select * from users_function(array_agg((select user_id from profiles)));

edited Feb 16, 2011 at 3:04

answered Feb 14, 2011 at 19:52

nate c

9,0532 gold badges29 silver badges28 bronze badges

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

3 Comments

Chirag Patel Over a year ago

I'm running PostgreSQL 8.4.4 and getting this error. Any ideas? select array_agg(select id from users limit 2); ERROR: syntax error at or near "select"

nate c Over a year ago

Add another set of brackets like: select array_agg((select id from users limit 2)). Forgot that - any time you have a subquery in a function you need two sets: one for the function an one for the subquery.

Chirag Patel Over a year ago

Sorry to keep bothering you, but this is what I got: select array_agg((select id from users limit 2)); ERROR: more than one row returned by a subquery used as an expression

Chirag Patel · Accepted Answer · 2011-02-22 17:45:07Z

1

I could not get the nate c's array_agg approach as I described above. This is an option:

select * from test_array('{1,2}');

CREATE OR REPLACE FUNCTION test_array(user_ids integer[])
  RETURNS void AS
$$
declare
begin
FOR i in array_lower(user_ids, 1) .. array_upper(user_ids, 1) LOOP
  RAISE NOTICE '%', user_ids[i]::integer;
END LOOP;
end
$$
LANGUAGE plpgsql;

answered Feb 22, 2011 at 17:45

Chirag Patel

5,9498 gold badges37 silver badges40 bronze badges

Collectives™ on Stack Overflow

How do I pass in the array output of SQL query into a PostgreSQL (PL/pgSQL) function?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related