sql - Select result into a type with array

Question

Having such type created:

create type type_student as (student_id numeric, first_name text, subjects text[]);

I am trying to create a function that would collect the students:

create function collect_students() returns setof type_student
as $function$
declare
   v_result type_student;
   v_student RECORD
begin
   for v_student in select id, first_name, (subject1, subject2, subject3) from students
   loop
      v_result := v_student;
      return next v_result;
   end loop;
end;
$function$ language plpgsql;

Where id, first_name, subject1, subject2, subject3 are columns in the students table.

I am getting casting errors connected with subjects value in the type_student type. Is there a way to do it without assigning each element of the array individually?

question from:https://stackoverflow.com/questions/65915706/select-result-into-a-type-with-array

Answer 1

subjects is declared as an array of text. Your SELECT returns a record type column, not an array.

The following should work:

select id, first_name, array[subject1, subject2, subject3] from students

---

However, you don't need a type, nor a FOR loop, nor PL/pgSQL for this:

create function collect_students() 
  returns table (student_id numeric, first_name text, subjects text[])
as $function$
  select id, first_name, array[subject1, subject2, subject3]
  from students
$function$ 
language sql;

If you do want to use PL/pgSQL you still don't need a (slow) FOR loop. A simple return query select ...; will do.