CODEWITHSUNDEEP
sqlpostgresql
calben
calben

Reputation: 1358

Sql Table Select from the results of a Postgres function

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!

Upvotes: 0

Views: 53

Answers (1)

GMB
GMB

Reputation: 222402

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;

Upvotes: 1

Related Questions