Getting an array return on function

Question

I would like to have this function return as an array which contains all ID (integer) from this query, but i am stuck here:

CREATE OR REPLACE FUNCTION public.all_id(
    prm_id integer)
    RETURNS SETOF integer 
    LANGUAGE 'plpgsql'
    COST 100.0
AS $function$
DECLARE
all_id integer;
-- all_id integer[]; gives an error, while integer only returns last value
BEGIN
    SELECT id
    COLLECT INTO all_id
    FROM subject_data
    WHERE sab_subject = (
        SELECT sab_subject
        FROM subject_data
        WHERE id = prm_id
        );
        RETURN NEXT all_id;
END;
$function$;

 SELECT * FROM public.all_id(1);

check the example apacioje -should give you the idea, how you should refactor the code... — Vao Tsun
– Vao Tsun, Commented May 23, 2018 at 10:53
You want returns int[] instead. Also: there is no need for PL/pgSQL for this. A simple SQL function will do just fine. — user330315
– user330315, Commented May 23, 2018 at 11:01
SELECT array_agg(id) FROM subject_data WHERE sab_subject = ( SELECT sab_subject FROM subject_data WHERE id = PRM_ID_VALUE ) — Vao Tsun
– Vao Tsun, Commented May 23, 2018 at 11:04

Vao Tsun · Accepted Answer · 2018-05-23 11:06:07Z

1

here's an example of fn:

t=# create or replace function ar() returns int[] as $$
declare ia int[];
begin
select array_agg(oid::int) into ia from pg_database;
return ia;
end;
$$ language plpgsql;
CREATE FUNCTION
t=# select * from ar();
                            ar
-----------------------------------------------------------
 {13505,16384,1,13504,16419,16816,17135,25542,25679,25723}
(1 row)

SETOF would return "table", language is not literal and thus does not require single quotes, and so on...

as a_horse_with_no_name correctly brings out, you don't need plpgsql for this. A simple query will work:

SELECT array_agg(id) 
FROM subject_data 
WHERE sab_subject = ( 

    SELECT sab_subject 
    FROM subject_data 
    WHERE id = PRM_ID_VALUE 

)

edited May 23, 2018 at 11:06

answered May 23, 2018 at 10:49

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

1 Comment

user330315 Over a year ago

You also don't need a plpgsql function for this. A simple SQL function will be sufficient

Collectives™ on Stack Overflow

Getting an array return on function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related