Re: Execute command in PL/pgSQL function not executing

Sorry for the late response and thank you Pavel for answering. This is my
first exposure to pgsql, so please bear with me. I am still getting the
Context message. Here is the modified function:

CREATE OR REPLACE FUNCTION survey_ct ()
RETURNS SETOF text 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));

RETURN QUERY 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;

This gives: CONTEXT: PL/pgSQL function survey_ct() line 15 at RETURN QUERY

Regards

On Thu, Nov 18, 2021 at 3:44 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> čt 18. 11. 2021 v 12:24 odesílatel Abdul Mohammed <
> imonikemohammed(at)gmail(dot)com> napsal:
>
>> Hello everyone,
>> 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:
>>
>> CONTEXT: PL/pgSQL function field_values_ct() line 15 at EXECUTE
>>
>> Please I would be very grateful for any hints as to what I could be doing
>> wrong.
>>
>
> This is not MS SQL - result of last query is not result of function.
>
> When you want to see result, you should to use RETURN statement - in this
> case RETURN QUERY EXECUTE, and your function should to return SETOF text
> instead VOID.
>
> Regards
>
> Pavel Stehule
>
>>
>> Regards
>>
>>
>>