Please I am having a problem with a function I am writing. The first part uses a loop that pulls the values from a column and concatenates them into a string. This first part works fine. The second part tries to use the string to build a pivot table using the crosstab function. The function is as follows:
CREATE OR REPLACE FUNCTION field_values_ct ()
RETURNS VOID AS $$
DECLARE rec RECORD;
DECLARE str text;
BEGIN
str := '"participant_id" integer,';
-- looping to get column heading string
FOR rec IN SELECT DISTINCT text
FROM question
ORDER BY text
LOOP
str := str || '"' || rec.text || '" text' ||',';
END LOOP;
str:= substring(str, 0, length(str));
EXECUTE 'SELECT *
FROM crosstab(''select sp.participant_id, distinct qu.text, sr.text
from survey_progress sp
join question qu
on sp.survey_id = qu.survey_id
join survey_response sr
on
qu.id = sr.question_id
where qu.question_type_id = 8
order by 1,2'')
AS final_result ('|| str ||')';
RAISE NOTICE 'Got to the end of the function';
END;
$$ LANGUAGE plpgsql;
The Execute Select statement doesn't seem to execute. There aren't any error or hint messages either. It only prints a context message as follows:
Please I would be very grateful for any hints as to what I could be doing wrong.
