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 stackoverflow.com/questions/11472790/… but I am not a postgres user so I can't be of more help than that — Ghost
– Ghost, Commented Feb 26, 2019 at 22:42

GMB · Accepted Answer · 2019-02-27 00:17:23Z

1

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;

answered Feb 27, 2019 at 0:17

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Sql Table Select from the results of a Postgres function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related