Postgresql function return multiple rows

Question

I need to add a function into my main query. The main query has where clause and for the 'IN' (example : Where id IN (3,4,2)) I need a function to insert into this IN which returns the required ids.

 CREATE OR REPLACE FUNCTION getItemID(groupid integer)   
 RETURNS SETOF item AS  $BODY$
 BEGIN
      RETURN QUERY 
      SELECT item_id
      FROM item
      WHERE group_id=groupid  
 END; $BODY$ 
 LANGUAGE plpgsql;  

 ALTER FUNCTION getItemID(groupid integer)  OWNER TO postgres;

This gives me an error

ERROR:  RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near "QUERY" at character ...  

How ever, can any one give any solution for this. I am a very new bee for functions. Also I need to test the values returned by the function are correct.

Answer 1

You use a older Postgres that doesn't support RETURN QUERY clause probably. Other issue is fact, so function is declared as RETURN SETOF record_type, but returns only scalar value (so maybe main issue is there)

Use a SQL function mentioned by Roman Pekar or use a RETURN NEXT statement instead

CREATE OR REPLACE FUNCTION getItemID(groupid integer)   
RETURNS SETOF item AS  $BODY$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM item WHERE group_id = $1
  LOOP
    RETURN NEXT r;
  END LOOP;
END;
$BODY$ 
LANGUAGE plpgsql;  

or

CREATE OR REPLACE FUNCTION getItemID(groupid integer)
RETURNS SETOF int AS $$
DECLARE _id item.itemid%TYPE;
BEGIN
   FOR _id IN SELECT item_id FROM item WHETE group_id = $1
   LOOP
     RETURN NEXT _id;
   END LOOP;
   RETURN;
END;
$$ LANGUAGE plpgsql;

For this use case a SQL function is better. Depends what do you do, and what is expected performance of your system, but these small (wrappers) function is not good idea usually. Create functions that implements some business instead.

Answer 2

why don't you use simple SQL function:

create or replace function getItemID(groupid integer)   
returns setof item AS
$BODY$
    select item_id, group_id
    from item
    where group_id=groupid;
$BODY$ 
language sql;

sql fiddle demo