Re: Execute command in PL/pgSQL function not executing

č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
>
>
>