Sql Table Select from the results of a Postgres function

Question

I have a function that generates an assessment information type defined below.

create type course_assessment_info as (
  total_count           int,
  mark_average          double precision,
  weight_sum            double precision,
  weighted_mark_average double precision
);

When I use my function for a single query, like below, I get the expected result.

select * 
from course_assessment_info_by_student_id_and_course_id('9be15896-40ca-46c6-8fdd-0ffe3bd79
586', '65dbdce1-fd76-4951-9db1-d089b3794d80');

Results :

 total_count |   mark_average    |    weight_sum     | weighted_mark_average
-------------+-------------------+-------------------+-----------------------
           1 | 0.834768535328714 | 0.540032932289522 |     0.450802499916595

When I use it on my larger function that uses a join, I don't get individual column names. My understanding is that I need to use the select * from course_asses... However, I'm having a hard time figuring out how to format that such that PostgreSQL doesn't throw a syntax error.

select course_assessment_info_by_student_id_and_course_id(s.id, c.id)
from student s
join student_course sc on s.id = sc.student_id
join course c on c.id = sc.course_id;

Thanks for any help!

your function should be in your FROM clause, not in your SELECT clause. Also, it helps to include the error your existing code is generating. It looks like you may need to use a LATERAL JOIN as per https://stackoverflow.com/questions/11472790/postgres-analogue-to-cross-apply-in-sql-server but I am not a postgres user so I can't be of more help than that — Ghost, Feb 26 '19 at 22:42

score 1 · Accepted Answer · answered Feb 27 '19 at 00:17

You seem to want LATERAL JOIN :

SELECT t.*
FROM student s
INNER JOIN student_course sc ON s.id = sc.student_id
INNER JOIN course c ON c.id = sc.course_id
INNER JOIN LATERAL course_assessment_info_by_student_id_and_course_id(s.id, c.id) t ON true

This should do it as well :

select (course_assessment_info_by_student_id_and_course_id(s.id, c.id)).*
from student s
join student_course sc on s.id = sc.student_id
join course c on c.id = sc.course_id;

Sql Table Select from the results of a Postgres function

1 Answers1