CODEWITHSUNDEEP
sqlpostgresql
Žilvinas Ringelė
Žilvinas Ringelė

Reputation: 104

Getting an array return on function

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);

Upvotes: 0

Views: 52

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51649

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 

)

Upvotes: 1

Related Questions