It’s a fairly big query. response is the culprit, everything else works great.
CREATE OR REPLACE FUNCTION get_exp_questionnaire_from_autoeval(i_entreprise_id UUID)
RETURNS TABLE(
thematique_name TEXT,
enjeu_name TEXT,
enjeu_description TEXT,
enjeu_image TEXT,
criteria_name TEXT,
criteria_description TEXT,
criterion_type TEXT,
criteria_id TEXT,
criterion_id TEXT,
question_id UUID,
question TEXT,
response TEXT
) AS $$
BEGIN
RETURN QUERY
WITH full_questionnaire AS (
SELECT
t.name AS thematique_name,
e.name AS enjeu_name,
e.description AS enjeu_description,
e.image AS enjeu_image,
c.name AS criteria_name,
c.description AS criteria_description,
cr.type::TEXT AS criterion_type,
c.id::TEXT AS criteria_id,
cr.id::TEXT AS criterion_id,
q.id AS question_id,
cr.question AS question
FROM
“Entreprise” ent
JOIN “Sector_Questionnaires” s ON ent.sector_id = s.sector_id
JOIN “Questionnaires” qn ON s.questionnaire_id = qn.id
JOIN “Questionnaire_context” qct ON qn.id = qct.questionnaire_id
JOIN “Question” q ON qn.id = q.questionnaire_id
JOIN “Criteria” c ON q.criteria_id = c.id
JOIN “Enjeu” e ON c.enjeu_id = e.id
JOIN “Thematique” t ON e.thematique_id = t.id
JOIN “Criterion_context” cct ON qct.id = cct.questionnaire_context
JOIN “Criterion” cr ON cct.criterion_id = cr.id
WHERE
ent.id = i_entreprise_id
ORDER BY
t.name, e.name, c.name, cr.order_type
),
auto_eval_responses AS (
SELECT
c.id::TEXT AS criteria_id,
cr.id::TEXT AS criterion_id,
r.display_text::TEXT AS response
FROM
“Evaluations” eval
JOIN “Evaluation_Response” resp ON resp.eval_id = eval.id
JOIN “Criteria” c ON resp.criteria_id = c.id
JOIN “Criterion” cr ON resp.criterion_id = cr.id
JOIN “Response” r ON r.id = resp.response_id
WHERE
eval.entreprise_id = i_entreprise_id
)
SELECT
fq.thematique_name,
fq.enjeu_name,
fq.enjeu_description,
fq.enjeu_image,
fq.criteria_name,
fq.criteria_description,
fq.criterion_type,
fq.criteria_id,
fq.criterion_id,
fq.question_id,
fq.question,
aer.response::TEXT
FROM
full_questionnaire fq
LEFT JOIN
auto_eval_responses aer
ON
fq.criteria_id = aer.criteria_id AND fq.criterion_id = aer.criterion_id
ORDER BY
fq.thematique_name, fq.enjeu_name, fq.criteria_name, fq.criterion_type;
END;
$$ LANGUAGE plpgsql;
